SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Following three questions are many times asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to another table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the methods over the cursor. Performance of following two methods is far superior over the cursor. I prefer to use Method 1 always as I works in all the cases.

Method 1 : INSERT INTO SELECT
This method is used when the table is already created in the database earlier and the data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them. I always list them for readability and scalability purpose.

USE AdventureWorks
GO
----Create testable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO



Method 2 : SELECT INTO
This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table. The new table is created with the same data types as selected columns.

USE AdventureWorks
GO
----Create a new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO



Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQLAuthority News – Book Review – Learning SQL on SQL Server 2005 (Learning)
Next Post
SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

Related Posts

677 Comments. Leave new

  • i have 2 tables simillar to this example.
    Table A Table B
    group_id group_id count
    1 1
    1 2
    2 19
    1 33
    2
    2
    19
    19
    33
    19

    i need to write an sql statement that will count the number of occurrences of each group_id # in table A and place that count in the count column of Table B.

    Thank you in advance,
    tom

    Reply
  • New techie Praveen
    July 13, 2009 3:14 pm

    Thanks Pinal,

    Will select into statement copy constraints also?

    Reply
  • We’ve found that OPENROWSET only ever returns 1 row in SQL Server 2008?

    For example, this code

    SELECT *
    FROM OPENROWSET(‘MSDASQL’,
    ‘DSN=the_dsn;UID=the_uid;PWD=the_pwd;’,
    ‘select 123 from dual union select 456 from dual’)

    … correctly returns 2 rows (123 and 456) in SQL 2000 but only returns 1 row (123) in SQL 2008.

    Is this a configuration issue? We’re connecting to an Oracle database.
    Thanks in advance

    Reply
  • Q. Write a SQL select statement. Given a table called EmailAddresses and the columns ID (int) and EmailAddress (varchar(100)), write a SQL SELECT statement that retrieves all email addresses that occur more than twice in the table.

    Reply
  • you are the best

    Reply
  • thank you! this article was very enlightening! i used the select into query combined with an update query to clone a table and swap values of two columns in a circumstance.

    SELECT *
    INTO [new-table]
    FROM [old-table]

    DECLARE @temp AS decimal
    UPDATE [new-table]
    SET @temp=value1, value1=value2, value2=@temp
    WHERE id < 806

    Reply
  • Hi there..!

    I want to know how to copy the data and its properties(identities)/structure from the old table to new table. I am really upset with this thing. When I was just using SELECT INTO FROM statement, It only copy the data with basic structure of the old table.. Kindly need your help.. thanks!

    Reply
  • SEND ME A FUNCTION PROCEDURE

    Reply
  • I tried by making TransactionOption to be Required.
    i want to insert a data throug child table
    for this i want to check if the data is all ready in master table than no problem,
    but if it is not in master table than i want ot insert data through child table.which is connect with the master table
    so please tell me what should i do

    Reply
  • Hi,

    how do i insert br_no?

    i have created a table1 mst_brng( br_no)pk
    i have created a table2 sec_mst_brng(br_no,br_mk)br_no is a FK
    and i want to insert the br_no in table2(br_no, br_mk)
    but i want check first if br_no allready in master table than no problem,
    but if it is not in master table than i want to insert data through child table so for this what should i do

    Reply
  • Hi Rushi,
    You are going right way. You have to use transaction.
    while inserting records in child table first you have to find that that record is already exist or not ,if not then first store into one local variable and then add it into master table first and then add in child table.

    I hope u will get it .

    Thanks
    Darshan Shah

    Reply
  • Hi Mitch,
    Basically we have to use select * into for temporary tables.
    We can use it to create basic structure with Data.
    But if you want to create table with all details then first you have to create script for it.
    GO Enterprise manager->right click on object ->Generate Script ->Create ->copy the script and use that script to create new object.
    after then use insert statment with select.

    I hope this will help you.

    Thanks
    Darshan Shah

    Reply
  • My tables are SubjectTable and GradeTable.SubjectTable has datafields of Course, Subject, Units While GradeTable has Studname, Subjects, Grade, Remarks. I inserted datafields of subject from SujectTable to GradeTable. And it leaves the other rows blank. How can I Add data to a Null rows?Can somebody help me with this problem of mine….thanks

    Reply
  • Hi Dave,

    How do we pronounce u’r name.

    thanks in advance. :)

    Reply
  • Hi pinal,

    i have a problem inserting multiple records into a table. My code looks like this:

    DECLARE @MyCursor CURSOR
    SET @MyCursor = CURSOR FAST_FORWARD
    FOR
    SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
    BEGIN TRAN t3
    BEGIN
    OPEN @MyCursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM @MyCursor
    INTO @StrengthAppMenInstIDP1
    END
    exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
    insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @StrengthAppMenInstIDP1)
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
    END
    IF @@error 0
    ROLLBACK TRAN t3
    COMMIT TRAN t3

    the select statement returns multiple values and i have to insert that into the same table. i know cursor is not a good way, but my select statement will always return a max of 10 values. Can you please check where i am going wrong, or a better way.

    Can you please help me with this as soon as possible. I would really appreciate this.

    Thanks,
    Harish

    Reply
  • FYI: in my test data, the select returns 2 values, but when i ran the query, i got only one row with @StrengthAppMenInstIDP1 as null, but it had a value.

    Dont know where i am going wrong…

    Harish

    Reply
  • @Harish

    Is there a way top combine this into one statement?

    INSERT INTO …. SELECT … FROM

    Reply
  • @Brian, hmm i dont think so. But i was able to figure out my issue.

    The correct one’s is:

    DECLARE @MyCursor1 CURSOR
    SET @MyCursor1 = CURSOR FAST_FORWARD
    FOR
    SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
    OPEN @MyCursor1
    FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
    exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
    insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @strength_app_men_inst_id)
    FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
    END
    CLOSE @MyCursor1
    DEALLOCATE @MyCursor1

    Thanks for checking out… Harish

    Reply
  • @Harish

    I’m happy you could figure it out.

    I always try making it one query if possible. I could not see it here, because a PROCEDURE is being used in the middle.

    INSERT…SELECT is usually faster than a CURSOR. And as one query, everything will be in context too. And for many, it is much easier to read because all the logic is in one statement. It might be worth a bit of your time to review the entire process and see if it can be one statement.

    Reply
  • Thanks Brian, for your time and effort, i’ll re-look into my stored proc and try to make it simple as you suggested…

    Thanks again,

    Harish

    Reply

Leave a Reply