sql insert statement

Question

Hi,
How to insert multiple records in a single  statement? what are the possible ways?
Thanks.

Senthilkumar · Answer

Hi,
There are lot of ways in sql server.
1)  If you are trying to insert from the selected records then
INSERT INTO tmpProducts(tProductID, tProductName, tManfacturer )
SELECT DISTINCT ProductID, ProductName, Manfacturer FROM Products
2) You can use the derived table to insert multiple records
SELECT * INTO #tmpProducts FROM Products
3) From sql server 2008 onwards
USE YourDB         GO         INSERT INTO MyTable (FirstCol, SecondCol)         VALUES ('First',1),         ('Second',2),         ('Third',3),         ('Fourth',4),         ('Fifth',5)
4) Using the union all
INSERT INTO MyTable (FirstCol, SecondCol)         SELECT 'First' ,1         UNION ALL         SELECT 'Second' ,2         UNION ALL         SELECT 'Third' ,3         UNION ALL         SELECT 'Fourth' ,4         UNION ALL         SELECT 'Fifth' ,5         GO

Vulpes · Answer

It's difficult to answer this in a general way as the INSERT statements for different RDBMS's may have different options.
However, for SQL Server, I'd check the examples in this link:
http://msdn.microsoft.com/en-us/library/ms174335.aspx