Tuesday, January 13, 2009

Multiple records insertion using a single Insert Stmt in MSSQL


If we need to insert multiple records, we would follow the traditional way 

INSERT INTO testtable  (Name, Nums)
 VALUES ('First',1);
INSERT INTO testtable (Name, Nums)
 VALUES ('Second',2);
INSERT INTO testtable (Name, Nums)
 VALUES ('Third',3); 

It is okay for 3 records, think of 100 0r 200 records . 
To avoid such a hectic process we can use UNION ALL

INSERT INTO testtable  (Name, Nums)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3



No comments:

Post a Comment