SQL Server 2000, SQL Server 7.0, SQL Server 6.5 and earlier
Views:
179566
Use this little trick to INSERT MULTIPLE row values instead of using multiple INSERT statements to improve performance and typing.
Terms of Agreement:
By using this article, you agree to the following terms...
You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
INSERT MULTIPLE row values instead of using multiple INSERT statements
I read an article on IBM´s homepage on DB2 where they had a tip on how to insert multiple row values with one statement like this INSERT INTO [TABLE] values ('Value1','Fernando'),('Value2','Carlos'), ('Value3','Vincent')
This is not possible in MS SQL. But there is a work around using multiple UNION ALL statements. See code bellow INSERT INTO[TABLE] select 'Value1','Fernando' UNION ALL select 'Value2','Carlos' UNION ALL select 'Value3','Vincent'
When inserting many values this should improve performance and typing. Enjoy FP
My example does work. If you only have two columns. As you know, if you specify the same amount of values as you have columns, then you do not need to specify the column names. My example could be rewriten like - INSERT INTO[TABLE] (column1,column2) select ('Value1','Fernando') UNION ALL select ('Value2','Carlos') UNION ALL select ('Value3','Vincent')
(If this comment was disrespectful, please report it.)
You can't use just a ; between statements. Then only the first row will be inserted. You have to do it like this. INSERT INTO[TABLE] (column1,column2) select 'Value1','Fernando' UNION ALL select 'Value2','Carlos' UNION ALL select 'Value3','Vincent' (If this comment was disrespectful, please report it.)
9/13/2004 12:21:44 PM:
I'd like to add some positive feedback and thank you for sharing a method of doing something in a different/more efficient way. :) (If this comment was disrespectful, please report it.)
9/20/2004 9:17:38 AM:
Wow! Very smart ai[Ÿžseful! That works well for me! Thanks Fernando! (If this comment was disrespectful, please report it.)
10/1/2004 8:27:53 AM:
That's very nice. Thanks for sharing. (If this comment was disrespectful, please report it.)
Thank you for sharing this code with is. I tested it using SQL Server 2000 using a test table and it worked exactly as you described. My test query was as follows:
INSERT INTO test_table SELECT 'test1' UNION ALL SELECT 'test2' UNION ALL SELECT 'test3' (If this comment was disrespectful, please report it.)
I cannot seem to make this work in an MS Access database, is there something else that needs to be in the syntax? (If this comment was disrespectful, please report it.)
nice code. although i bet if we were to race, i could copy/paste a lot faster than you could type 'union all'!!! just kidding! thanks! (If this comment was disrespectful, please report it.)
2/10/2005 5:50:53 AM:
For Oracle : insert into myTable (champ1, champ2,.. ) select '9999999', to_date('01.12.04','DD.MM.YY'),.. FROM DUAL union all select '9999999', to_date('01.12.04','DD.MM.YY'),.. FROM DUAL union all.... (If this comment was disrespectful, please report it.)
2/15/2005 11:52:13 PM:
this methode only work if the rows is lest than approx 1000. if used to rows greater than it, it fails and comes with (If this comment was disrespectful, please report it.)
2/28/2005 8:08:02 AM:
In which way it is more faster then Batch Inserts? (If this comment was disrespectful, please report it.)
On a client you can use this code to send one batch to the server instead of sending multiple commands. (If this comment was disrespectful, please report it.)
3/1/2005 12:48:42 AM:
I agree, but I am talking in the terms of execution. I think that while execution it will also iterate throught every values.... and put it as dynamic parameter and execute it. If it's also doing like that then there's no difference between this procedure and batch updates. Because there also it is creating seperate values stmts and execute it. Execution plan will be created in both the ways. (If this comment was disrespectful, please report it.)
5/25/2005 1:13:03 PM:
Viejo, buen codigo mae.. esta tuaniz.. funciona muy bien... asi es como lo utilice..
INSERT INTO [TABLA] ([TCAMPO_1],[TCAMPO_2]) select 1,4 UNION ALL select 1,15 UNION ALL select 3,19
Exelente... Pura VIda (If this comment was disrespectful, please report it.)
Great idea Fernando. I came up empty through google (except for your link) MS SQL books on-line, MS sites, ... And I hated the idea of executing so many dang inserts.
Kudos (If this comment was disrespectful, please report it.)
It's ten times faster than multiple Inserts executed in batch (using for example Datasets and SqlDataAdapter.UpdateBatchSize;) unfortunately if the primary key value is generated by the server you cannot get it, the dataset can... (If this comment was disrespectful, please report it.)
INSERT INTO tblTest (test1, test2, test3, test4, test5) SELECT 1,1,1,1,1 UNION ALL SELECT 2,2,2,2,2 UNION ALL SELECT 3,3,3,3,3
Hi, above is the code I've written, but I keep having a "syntax error (missing operator) in query expression '1 UNION ALL SELECT 2'. Anyone can tell me what's the problem? (If this comment was disrespectful, please report it.)
View a smart way of inserting multiple rows in one go here http://www.cyberminds.co.uk/blog/articles/how-to-insert-multiple-rows-in-sql-server.aspx (If this comment was disrespectful, please report it.)
(If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
By 12 Users
9/1/2004 2:20:17 PM: 
