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)
677 Comments. Leave new
@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
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
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!
Very nice blog. I appreciate your efforts. It works for me.
Can you please let me is it possible with one query?
Kunal Mehta
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.
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
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.
I’ve now opened connections to both databases but still have no success. (Neither database has a password….)
Love you work Dave,
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
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..
How do you read data automatically from a table with a newly inserted data on it?
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
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
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′
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
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.
Thanks Mr Imran Mohammed, It is working I was missing a dot, it should be double dot
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!
@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.