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)

,
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

  • What do you do with timestamp erorrs? Using * instead of implicit fields to save time.

    “Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.”

    Reply
  • Muchas gracias amigo.

    Reply
  • Yahh!! Really Its working.Thanks

    Reply
  • Hi,
    i am creating procedure like this:

    ALTER PROCEDURE [dbo]. A
    @currentDate DATETIME
    AS
    BEGIN
    Delete from TT_21_STOCK_01_STATUS

    INSERT INTO TT_21_STOCK_01_STATUS(BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
    STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
    STK_Total_OUT)
    SELECT BinCode, SKUCode, BatchCode, ConfirmedQuantity, UnconfirmedPutaway, UnconfirmedPicked, BlockedQty, STK_Defragmentation_IN, STK_Replenishment_IN,
    STK_Aggregation_IN, STK_Optimize_IN, STK_Total_IN, STK_Defragmentation_OUT, STK_Replenishment_OUT, STK_Aggregation_OUT, STK_Optimize_OUT,
    STK_Total_OUT FROM TT_21_STOCK_02_BACKUP where StockBackupDateTime= @currentDate

    END
    procedure execute correctly but can’t insert record…….
    plz Help me

    Reply
  • I am trying to copy data from one server’s view to another’s table. I am sure they have the same structure. For security reasons, the servers CAN NOT be linked.

    I tried to use MS_Access. I cannot import the data to there and upload to newtable, but cannot because MS_Access limits to 255 columns.

    I tried linking the two tables and doing a DoCmd.RunSQL (“INSERT newtable SELECT * FROM oldtable”) and it failed with error 3162 (inserting a null into a non-variant).

    Any suggestions? THANKS!

    Reply
  • Sir; I’m new to SQL Server so as I type SQL statements that I’ve used on other platforms, I find syntax errors. I google my problem and keep ending up on this web site. Thanks so much from Memphis for all the help you’ve given me!

    Reply
  • i have table1(idno, firstname, secondname, lastname, age, address)
    insert three records to this table?
    create other table2 consist of having columns(age, firstname, ido)
    insert into the data of table1 into table2?
    quick answer

    Reply
  • sir, i want to copy only two column from table1 to table2 and rest all column in table2 i want to filled it dynamically through query.plese help me how can i fetch only two column data from table 1 and insert into table2 along with other data in table 2.i have 5 column in table 1 and 5 column in table 2 in which two columns in both table are same and i want to copy these two columns in to other table.

    Reply
  • HI Dave,

    I’m trying to do this.

    INSERT INTO table_1 (column_1, column_2, PlusOneMoreColumn) VALUES
    (SELECT * FROM table_2, “ValueForTheExtraColumn”)

    table_1 has column_1, column_2, PlusOneMoreColumn
    table_2 has column_1, column_2

    Is this possible?

    Reply
  • Ravi Shankar Mandal
    June 30, 2013 7:24 am

    Sir, my purpose not solved at all. i need to copy a table to a new table i.e. not existing. with the same data type, constraint and keys into the destination table. but this code copies only data and same datatype

    Reply
  • hi
    Actually am working on two databases one is staging and the other is present DB. I created some temp tables in present one now i need to map the data with the tables present in staging DB .Please help me out. How to map them using TOAD MYSQL

    Reply
  • Hi Pinalbhai,

    This is Vishal From Ahemedabad.

    I have been facing a strange problem since last few months. A query is executed from a client machine. I use INSERT INTO SELECT method to insert records from one table to another (Both tables are in separate databases.) in my application.

    e.g.
    INSERT INTO TestTable (FirstName, LastName)
    SELECT Top 20000 FirstName, LastName
    FROM Person.Contact
    WHERE EmailPromotion = 2

    As per above query 20000 records should be inserted in the targeted table. but sometimes It inserts 40000 or 60000 or 80000. means all records are inserted in multiple times (either 2times, 3times or 4 times). I did not get the exact reason behind this, i have surfed lots of pages on internet but i have not come across any solution.

    Please do needful……

    Reply
  • Thanks for that post, it solved a long existing problem on our production servers! :-)
    Regards

    Reply
  • hello i am new at mysql.i want a piece of advise.
    i have two tables country.sql and users.sql and i create a form
    i want to put id_country from the country table to users table.
    i write this:$q= “INSERT INTO `users` (`id_country`) VALUES (‘(SELECT users.id_countryFROM users JOIN country ON users.id_country=country.id_country)’)”;
    please help me because i don’t know what to do.

    Reply
  • saurabh titus
    July 17, 2015 2:46 pm

    sir i have a question. i have two tables t1 and t2. and one form with one textbox. Fields are name,age,city . both tables have same fields. my question is how i search record from both tabls.It is important ot link both tables. or is their any method to search record without linking both these tables.

    Reply
  • my problem for i have one table (single column) only how i can spread the data store the another table in multiple column ?

    Reply
    • Deva – you need to write a select query to split one column into multiple. Then use INSERT…SELECT to achieve whatever you want.

      Reply
  • Thank u ji @ Dave

    one small error :

    am create one table (column1(varchar), column2(varchar), column3(numeric), column4(numeric), column5(numeric) );

    what is the problem for (column3 ) insert the numeric data but its displaying error? at the same column4 and column5 error problem ( insert the numeric values only)
    but i need data type numeric
    how can i find the solution ??

    Reply
  • Hi all,

    The following query works fine but I have a different requirement.

    My requirement is as follows.
    I have table1 and table2, table1 contains 5 columns and table2 contains 3 columns. Now, we need to pull data from table2 and insert into table1. Here in table2 contains only 3 columns and table1 contains 5 columns. So the rest should insert with some default values.

    Could someone help me how to write a query for this.

    Reply
  • Jayanta Moitra
    August 1, 2015 5:17 pm

    I am trying to insert a specific field data from one table to another using INSERT command. The program is running error free but the data is not getting inserted. In sql its running ok & data is getting inserted but when I am trying it from ingres program the program is running ok but data not getting inserted.

    Reply
  • I’m having a Table called Test_Pivot which looks like
    F_SN F_Title F_Data
    1001 TRX RRN 301807460001.00
    1001 DATE TIME 18:07 ,30/04/2015
    1001 AUTH CODE
    1001 AMOUNT 0.22
    1001 SCHEME NAME SPAN
    1001 RESULT DECLINED
    1011 TRX RRN 301920050002.00
    1011 DATE TIME 19:20 ,30/04/2015
    1011 AUTH CODE
    1011 AMOUNT 0.01
    1011 SCHEME NAME MASTERCARD
    1011 RESULT DECLINED

    The output should look like this:

    TRX RRN DATE TIME AUTH CODE AMOUNT SCHEME NAME RESULT
    301807460001.00 18:07 ,30/04/2015 0.22 SPAN DECLINED
    301920050002.00 19:20 ,30/04/2015 0.01 MASTERCARD DECLINED

    Reply

Leave a Reply

Menu