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

  • Dharemendra Dixit
    January 2, 2009 1:18 pm

    @devan

    Hi Bro,

    You can solve Your problem by writing this Script, think it will work successfully..

    SELECT * INTO MYTable FROM Pubs.dbo.TableName

    Regards
    Dharmendra Dixit
    ASKSQLEXPERTS

    Reply
  • You the man Pinal!!

    I was looking to copy tables in sql server, and I got the way quickly from your website.

    Thank you.

    Kevo

    Reply
  • Hi,

    Could anybody help or guide me on how to automate the backups of our tables from all the databases in Sql server 2000 and storing that backup file as *.mdb(MS Access) file on daily basis?

    Thank you!

    Reply
  • Very nice blog. I appreciate your efforts. It works for me.

    Can you please let me is it possible with one query?

    Kunal Mehta

    Reply
  • How to insert data from one SQL server table to another SQL server table?
    For example, I want to recreate the table on production server from test server. Both servers are SQL server 2005 express, table structure are same.

    Reply
  • Hi

    I’m trying to transfer records from one table in one database to a parallel table in a second database, using code as described in responses above.

    I’m using the code :

    “INSERT INTO [Students] IN ‘C:VB9Future Indicativefiarchive2007.mdb’ SELECT * FROM [Students] IN ‘C:VB9Future Indicativefi.mdb where [ID] in (2626,3548,2627,3549)”

    and I get an error ‘Syntax error in from clause’. Can you spot a fault????

    I have an open connection to the first database, but have NOT iopened a connection to the second database. Is this required????

    Many thanks

    Rory

    Reply
  • Further to that… I note that I have a connection open to the database FROM which I’m copying, but do NOT have an open connection to the destination database. This is clearly wrong… I’ll experiment.

    Reply
  • I’ve now opened connections to both databases but still have no success. (Neither database has a password….)

    Reply
  • Love you work Dave,

    Reply
  • satish kumar singh
    January 28, 2009 1:25 pm

    Hi,

    I retrive data from table using mysql database with the c++ program under linux , but when i insert data using variable then table receive null value

    Reply
  • can you help me…
    this is a simple problem that i can’t figure it out.

    syntax:

    strql=”INSERT INTO tablename (field, field) VALUES (” & text1.text & “, ” & text2.text & “)”

    unable to save in the database..

    Reply
  • How do you read data automatically from a table with a newly inserted data on it?

    Reply
  • Automate sql Query(stored procedure) using Jobs

    Just create a new job through sql server agent
    and in description write down

    EXEC Stored procedure name

    Reply
  • Pritesh Parmar (Pintoo)
    February 10, 2009 4:52 pm

    Automate sql Query(stored procedure) using Jobs

    Just create a new job through sql server agent
    and in description write down

    EXEC Stored procedure name

    Reply
  • Pritesh Parmar (Pintoo)
    February 10, 2009 4:56 pm

    This Query is for AUTOMATION OF STORED PROCEDURE

    WHILE CREATING JOBS THROUGH SQL SERVER AGENT

    EXEC sp_makewebtask
    @outputfile = ‘D:\Pritesh\Media WIP Balances as of 31 Jan 2009.xls’,
    @query =’Select ad_PCCode, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
    case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end as Stat_Name,
    sum(ad_dramt) as WIP_DR,
    sum(ad_cramt) as WIP_Cr,
    sum(ad_dramt – ad_cramt) as WIP_Bal
    From WIPMediaById
    left outer join qryBrands on ad_BrandCode = Brand_Code
    left outer join mismasterids on ad_statusid = mis_misid and mis_group = ”MSTAT”
    Where Ads_AccDate <= ”2009-01-31” and ad_accode = ”1320000500”
    Group by ad_PCCOde, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
    case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end’,
    @colheaders =1,
    @FixedFont=0,@lastupdated=0,@resultstitle=’Media WIP Balances as of 31 Jan 2009′

    Reply
  • Mustaque Ahemed
    February 12, 2009 4:54 pm

    I am trying to insert data in a table table1 of data base db1 from table 2 of database db2 . But I find error message 208 invalid object name

    insert into tb1(f1,f2) select f1,f2 from ma.tb1 where ma.tb1.f1=1

    Reply
  • Mustaque Ahemed

    you have databaseA , TableA

    you have databaseB, TableB

    say, you are trying to insert data from TableA of DatabaseA into TableB of DatabaseB

    This is script,

    use DatabaseB
    insert into TableB (f1,f2) select f1,f2 from databaseA..TableA as Tab1 where Tab1.f1 = 1

    Regards,
    IM.

    Reply
  • Mustaque Ahemed
    February 14, 2009 9:28 am

    Thanks Mr Imran Mohammed, It is working I was missing a dot, it should be double dot

    Reply
  • based on this example i used the following code:

    insert into entrydata
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    select
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    from holdingtable where StaffID = ‘AMBRES003’ and WeekBeginningDate = ‘2009-01-26’;

    i recieved the following error message:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘,’

    i have checked the code and don’t see any erroneous ‘,’ and there are no ‘red lines’ under any of the code in SSMS – I am using SQL Server 2008 if that helps

    any idea what the problem is? i have wasted a whole afternoon trying to copy these columns from one table to another!

    Reply
  • @Dave

    insert into entrydata
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    select
    StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate from holdingtable where StaffID = ‘AMBRES003′ and WeekBeginningDate = ‘2009-01-26′

    When you are writing insert into table_name it should be immediately followed by a select statement.

    insert into table_name select col1,col2,col3 from table_name2
    where blah blah….

    Regards,
    IM.

    Reply

Leave a Reply