views:

1792

answers:

8

I have been working with TSQL in MSSQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax

INSERT INTO myTable <something here>

I understand that keyword INTO is optional here and I do not have to use it but somehow it grew into habit in my case.

My question is:

  • Are there any implications of using INSERT syntax versus INSERT INTO?
  • Which one complies fully with the standard?
  • Are they both valid in other implementations of SQL standard?
+1  A: 

They both do the same thing. INTO is optional (in SQL Server's T-SQL) but aids readability.

DOK
+20  A: 

INSERT INTO is the standard. Even though INTO is optional in most implementations, it's required in a few, so it's a good idea to include it to ensure that your code is portable.

You can find links to several versions of the SQL standard here. I found an HTML version of an older standard here.

Bill the Lizard
The book "SQL-99 Complete, Really" says that Sybase (and hence MS SQL Server) behave in the nonstandard way, allowing INTO to be optional. Other brands of database require the keyword.
Bill Karwin
Right. If you always use INTO, you don't need to remember which ones consider it optional. All implementations allow it to be used.
Bill the Lizard
+7  A: 

It may be optional in mySQL, but it is mandatory in some other DBMSs, for example Oracle. So SQL will be more potentially portable with the INTO keyword, for what it's worth.

Tony Andrews
+3  A: 

They are the same thing, INTO is completely optional in T-SQL.

Contrary to the other answers, I think it impairs readability to use INTO.

I think it is a conceptional thing. I am not inserting a row into a table named "Customer", but I am inserting a "Customer".

If you follow the first conception, "INSERT INTO Customer" would most likely "feel right" for you.

If you follow the second conception, it would most likely be "INSERT Customer".

Tomalak
I think the risk with your approach is that you may lead new programmers to confuse objects with tables.
Dave DuPlantis
Your answer is factually correct. It's a shame when people down-vote opposing points of view. I think one of the benefits of SO is hearing those difffering opinions.
DOK
Maybe. That is why I used "I think" and "its a conceptional thing", showed both ways to do it, leaving the decision what is best for you open. Read again if you think I said you should do it one way or the other.
Tomalak
@dok1.myopenid.com: Thanks for backing me up.
Tomalak
+1  A: 

I prefer using it. It maintains the same syntax deliniation feel and readability as other parts of the SQL language, like "group BY", "order BY".

AgentThirteen
+2  A: 

One lesson I leaned about this issue is that you should always keep it consistent! If you use INSERT INTO, don't use INSERT as well. If you don't do it, some programmers may ask the same question again.

Here is my another related example case: I had a chance to update a very very long stored procedure in MS SQL 2005. The problem is that too many data were inserted to a result table. I had to find out where the data came from. I tried to find out where new records were added. At the beginning section of SP, I saw several INSERT INTOs. Then I tried to find "INSERT INTO" and updated them, but I missed one place where only "INSERT" was used. That one actually inserted 4k+ rows of empty data in some columns! Of course, I should just search for INSERT. However, that happened to me. I blame the previous programmer IDIOT:):)

David.Chu.ca
A: 

If available use the sandard function. Not that you ever need portability for you're particular database, but chanses are you need portabilitiy for you're SQL knowledge. A particular nasty T-SQL example is the use of isnull, use coalesce!

Tom
+3  A: 

In SQL Server 2005, you could have something in between INSERT and INTO like this:

INSERT top(5) INTO tTable1 SELECT * FROM tTable2;

Though it works without the INTO, I prefer using INTO for readability.

Chenster