SQL SELECT INTO Vs INSERT INTO Statement
INSERT INTO SELECT and
SELECT INTO are extremely similar procedures, there are some significant distinctions between them.
I occasionally observe folks who are perplexed by the similarities while overlooking the distinctions. So here's a brief checklist for you.
SQL SELECT INTO Statement
SELECT INTO findings will be saved in a new table called MyTable.
Each of the columns in the table will have a name if all of the columns in the query are named. This is the most typical error I encounter while using this command.
The source query determines the data type and nullability.
If one of the source columns is an identity column and certain conditions are met (no
JOINs in the query, for example), the new table's column will be an identity as well.
Example: Copy only a few columns into the new table:
SELECT BookName, BookID INTO Author FROM Book;
SQL INSERT INTO Statement
MyTable must already exist for INSERT INTO to work.
Example: Insert data from one table into another for specific columns,
INSERT INTO Customer2(Name,Address) SELECT Name,Address FROM Customer;
SQL DIFFERENCE BETWEEN INSERT INTO AND SELECT INTO STATEMENT
There are 5 main distinguish select into Vs insert into statement:-
|SELECT INTO||INSERT INTO|
|In SQL, the SELECT INTO statement is used to replicate data from one table to another.||The SQL command INSERT INTO SELECT is used to copy data from a source table and insert it into a destination table. However, the target table must exist in the database before the data can be copied.|
|SELECT INTO creates a new table and populates it with data.||INSERT INTO SELECT inserts into a table that already exists.|
|The SELECT...INTO command will produce new pages for table construction in the same way that ordinary tables do, and they will be physically removed when the temporary table is dropped.||For insert/update/delete operations, the INSERT...INTO command will reuse data pages created in cache. When it is dropped, it will also truncate the table.|
|SELECT...INTO command, which performs better than INSERT...SELECT command in most cases.||INSERT INTO is a command that is used to insert text into a document. The SELECT...INTO command isn't any better.|
|In both the simple and bulk logged recovery models, SELECT INTO is minimally logged.||Because it is a fully logged transaction, INSERT INTO is slower.|