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