3

Could some one please tell me the difference between INSERT INTO and only INSERT commands in terms of performance. I only know that INSERT INTO executes faster and INSERT takes less Network Bandwidth.

Could you please explain in detail so that I should be able to judge where to use INSERT INTO and where to use only INSERT.

juergen d
  • 201,996
  • 37
  • 293
  • 362
Iftekhar Ilm
  • 81
  • 1
  • 9
  • The `INTO` is optional in the `INSERT` statement. – Gordon Linoff Jul 27 '16 at 12:08
  • INTO is optional I know, I am asking difference in terms of performance, which one performs better – Iftekhar Ilm Jul 27 '16 at 12:19
  • 4
    Possible duplicate of [INSERT vs INSERT INTO](http://stackoverflow.com/questions/233919/insert-vs-insert-into) – ehh Jul 27 '16 at 12:21
  • if you omit the INTO then your command string is potentially shorter, meaning any SQL string transmission from client,command parsing or disk holding uncompiled code is slightly smaller - if you've got access to the ISO language definition, you can check if either meets the ISO standard – Cato Jul 27 '16 at 13:28

2 Answers2

4

There is no difference. It is the same command. The into word is optional part in the syntax

James Z
  • 12,209
  • 10
  • 24
  • 44
  • INTO is optional I know, I am asking difference in terms of performance, which one performs better. – Iftekhar Ilm Jul 27 '16 at 12:14
  • 1
    There is **no** difference. Like I said, it is the **same command** – James Z Jul 27 '16 at 12:22
  • @Pred 'its' is a possessive form, e.g. The cat wagged its tail, never 'the cat wagged it is tail' though - it's = it is - take care with apostrophes in SQL and outside it too. – Cato Jul 27 '16 at 13:18
  • @AndrewDeighton true, my bad. conclusion: read again before post. Deleted it (since can't edit after 5 mins) – Pred Jul 27 '16 at 13:28
0

There wouldn't be any "difference in terms of performance," because the two are identical.

Now, what would make a difference in the performance of an INSERT? ...

BEGIN TRANSACTION ... INSERT INSERT INSERT ... COMMIT.

A transaction brackets "an atomic unit of work" and, in so doing, sometimes allows the database engine to perform writes more efficiently. (But, it also has implications of locking, due to their so-called "transaction isolation level" options.) I refer you to the documentation for a further discussion of it.

Mike Robinson
  • 8,490
  • 5
  • 28
  • 41
  • I checked the results of both INSERT INTO and only INSERT using Client Statistics, and YES there is difference in the performance. INSERT INTO Executes faster, where in only INSERT takes lesser Network bandwidth. So its wrong to say there is no difference. – Iftekhar Ilm Jul 28 '16 at 11:19
  • FYI, this *is* incorrect. The official syntax of the T-SQL `INSERT` statement is found here: https://msdn.microsoft.com/en-us/library/ms174335.aspx. This page definitively states that `INTO` *"is **an optional keyword** that can be used between INSERT and the target table."* ## However, it is quite easy to (think that you) "see" empirical evidence of a difference, although the `EXPLAIN` statement will demonstrate otherwise. In standard SQL, and in most other implementations, the keyword is required. – Mike Robinson Aug 02 '16 at 01:34