Insert into: use values() or SELECT

Question

Insert into: use values() or SELECT

piet.kouwer

Old Hand

Points: 354
More actions
December 31, 2010 at 1:33 pm

#229236

Hi all,
perhaps this question had been asked before, but I don't exactly know which keywords to use on the search-engine:
Sometimes I read code (e.g. on the Internet) to insert data into a table. Sometimes developers use, for exampel "VALUES (a,b,c,)" sometimes the will use "SELECT a,b,c"
What's the exact difference between these two methods and why use one instead of the other ?
Thanks and for later this day a happy and healthy 2011 !!
Piet Kouwer.
the Netherlands

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

piet.kouwer (12/31/2010)
Hi all,
perhaps this question had been asked before, but I don't exactly know which keywords to use on the search-engine:
Sometimes I read code (e.g. on the Internet) to insert data into a table. Sometimes developers use, for exampel "VALUES (a,b,c,)" sometimes the will use "SELECT a,b,c"
What's the exact difference between these two methods and why use one instead of the other ?
Thanks and for later this day a happy and healthy 2011 !!
Piet Kouwer.
the Netherlands

In the VALUES clause, you are specifying the values to be inserted.

In the SELECT, you can explicitly specify the values. However, you can also specify columns from a table.

Prior to SQL 2008, if you had > 1 row to insert, you would either have multiple INSERT ... VALUES statements or multiple INSERT ... SELECT statements to insert one row at a time; or one INSERT statement with multiple SELECT statements UNIONed together.

In SQL 2008, you can now have multiple VALUES... INSERT INTO MyTable (col1, col2) VALUES (1,2), (2,3), (3,4), (4,5), which is similar to the INSERT ... SELECT UNION construct above.

Does this answer your question?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

If your developers are using temporary tables or want to create a new table you can use a SELECT INTO which will create the table with columns matching the data types returned by the SELECT statement. Thus by passing the need to use a create table statement, saves the developers a lot of typing.... not necessarily a good thing, it just depends on the developers objective.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]