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
Dear Pinal,
Thanks for this.
I want to transfer table of one data of first database to table of second database using stored procedure.
Will u please send me the answer on my mail id.
Thanks
hi
i am trying to insert data from one table to another table with same field name but the datatype for one field is different that is for TravelDate field in the 1st table it is Varchar(15) & in the 2nd table the Datatype of TravelDate is Datetime so when im trying to insert data from 1st table to 2nd table im getting error as
“Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.”
when im trying to insert from this query
insert into linkinvoicesector(InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag)
select InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag
from linkinvoicesectors
i have tried even by use convert while inserting but no use still same error.
can i have any solution regarding this problem. I would be very thankfull to u if my problem is solved
thankq
If I Have Two Databases On Two Different Server Connected With Each Other And I Have Got To Make View How Can i Collect Data From Both Databases I Mean What is The Way to Make Connection Between Both Databases?
excellent post! helped me tons.
thanks.
Thanks very much for this post i have been struggling but am finally getting somthing. My problem is the query below is repeating each record twice. what could be the problem.
Select CandTrans.CentCode,CandTrans.CandNo,CandTrans.Subjcode,CandTrans.CandName,CassMarks.SubjName
FROM CandTrans,CassMarks
WHERE NOT EXISTS(SELECT CassMarks.CandNo FROM CassMarks WHERE(CandTrans.CandNo=CassMarks.CandNo))
Hi,
i am working on asp with backend as sqlserver 2000
I have a diff problem
i hav two tables for masters one is main and another is history table
when some one modifies or updates the existing record
the data of the existing record will be copied to the history table.
but when iam using this below query
insert into NRI_PIS_SCHEME_MASTER_HISTORY select SchemeId,Narration,Hslbrokper,
BankPisPer,DPper,convert(datetime,StartDate,103), convert(datetime,EndDate,103) ,
Remarks,Seqno , CreatedBy,CreatedDate,’aurionpro’,getdate(),’0′,’1′ from
NRI_PIS_SCHEME_MASTER where SchemeId= ’15’
the data is inserted twice
i checked out the query it got displayed only once
i donno what’s going on
i tried diffly but nothing going in the right way pls help me
thnx in advance :)
How To Protect Password in database using sql query
2005.
Pl help me.
I want to insert data from one server database table
to another server database table in sqlserver 2000.
pls help me…
@Jitender
You can use any one these to get your task done.
1. You can use DTS Package and using that DTS package you can import data from one sql server and load it into another sql server.
2. You can create a linked server on any of the server, lets say you have a table1 on Server1 and table2 on server2, you want to copy data from table1 on server1 to table2 on server2.
First you create a linked server, on server1 (name of linked server lets says is Server1) after you create this, execute this script on server2
insert into table2 select * from server1.db_name.dbo.table1
This should work fine, you have to change db_name in above query with the database name on server1 in which table1 is present.
if you do not want to create a linked server because of security reasons, you can also use OPENROWSET Function. Read more about OPENROWSET in Books online, look at sample script provided in books online, That should give you a good start.
IM
hi, why my insert do not work??
INSERT INTO psi_Checklist_Logs
(application_id, checklist_id, data_month, activity_status, execute_Date, operation_status, summary, bgcolor, boxicon, starttime, endtime, checklist_code, application_code)
SELECT APPLICATION_ID, OPERATIONCHECKLIST_ID, 200903, isnull(ACTIVITY_STATUS,”), EXECUTE_DATE, isnull(operation_status,”), isnull(COMMENTS,”), ”, ”, [start], [end], CheckCode, AppCode FROM psi_Checklist_Logs_bk
Msg 512, Level 16, State 1, Procedure trig_Checklist_Logs, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.
thank you
Hi All,
I need to selectively copy records from one table to another i.e. copy if the record does not exit otherwise update a particular field.
I guess I have to use cursor in a stored procedure. But I’m not sure how. Would appreciate if someone can put some code here.
I’m using Firebird database.
Thanks in advance.
@Ali Kazim
You need not use Cursors for this task,
Instead of one query write two quries,
1. Query to update existing Records
2. Query to Insert New records
Example Script :
create table example1 ( eid int , ename varchar(10))
insert into example1 values ( 1, ‘Apple’)
insert into example1 values ( 2, ‘Boy’)
insert into example1 values ( 3, ‘Cat’)
insert into example1 values ( 4, ‘Doll’)
select * from example1
create table example2 (eid int, ename varchar(10))
insert into example2 values ( 1, ‘Apple’)
insert into example2 values ( 3, ‘Boy’) — Needs to be updated.
select * from example2
— Update Using Joins
update example2
SET ename = B.ename
from EXAMPLE2 A
join example1 B on A.EID = B.EID
— Insert Using Joins
insert into example2
select A.eid
,A.ename
from example1 A
full outer join example2 B on A.eid = B.eid
where B.eid is null
— Update Using Where & IN Clause
insert into example2
select A.eid
,A.ename
from example1 A
where A.eid not in (select eid from example2)
— Insert Using Where & IN Clause
update example2
set ename = A.ename from example1 A
where A.eid in (select eid from example2)
Some times Joins are faster than In and where clause, in some rare cases joins could be slower. That is the reason I have provided you examples using both, Joins and where clause,
Check which one suits best for you consider only performance, both scripts will and should return same result set.
One Suggestion: I read this comment some where, long back, Cursors and While Loops are for freshers.
Use while loops and cursors carefully, they can kill your performance like anything.
~ IM.
Hi,
I how do i insert guid?
i have created a table1( guid, field1)
and i want to insert the guid in table2(field2, field3, guid)
table1 has guid as PK
i am having problems inserting record in table2, i want to insert the guid from table1 into table2.
Thx for this one, it’s really interesting
Thx again
pls reply me i want to connect my excel file with my sqlserver 2005,
so tell me how to that!!
Thanks for this article, However it inserting new records into an existing child table is not working for me because it is dependent on a parsed version of one of the parent table fields. And because I use a table function to achieve this. How can I fix?
I have SQL Server 2008 and Visual Studio 2008 and am trying to automatically insert new records into a child table based on new inserts into its parent table.
I know that this should be simple: via an insert trigger. However, the complexity is that the new records into the child table need to be a parsed version of one of the parent table’s fields.
To achieve this, I developed a table function which parses this one field in the parent table.
Originally I had the Foreign Key defined below, but this wasn’t automatically inserting new records into my child table, so I commented it out.
I have tried writing both recursive and non-recursive insert triggers, but I have received errors on almost every attempt. I was able to partly get non-recursive triggers to work…but without the parsing function. I think the trick is that this is a table function.
How should I design this? Some of my pseudo code as follows:
CREATE TABLE ParentTable
(
ParentID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
strInput VARCHAR(8000) NULL
)
CREATE TABLE ChildTable
(
ChildID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
ParentID INT,
FullRow VARCHAR(8000)
–I removed the following FK cause it wasn’t automatically inserting new records:
–CONSTRAINT AddParentID FOREIGN KEY ParentID REFERENCES ParentTable(ParentID)
)
Sample output of what I want is:
ParentTable:
ParentID strInput
1 ‘AAA|BB|CCCCCC|D|EE’
2 ‘FFF|R|BC|D|EE’
3 ‘GG|BB|CCHC|D|EE’
ChildTable:
ChildID ParentID strInput
1 1 ‘AAA’
2 1 ‘BB’
3 1 ‘CCCCCC’
4 1 ‘D’
5 1 ‘EE’
6 2 ‘FFF’
7 2 ‘R’
8 2 ‘BC’
9 2 ‘D’
10 2 ‘EE’
…
My table-function removes the “|” delimiter with strInput as its input. How can I achieve the above results?
Thanks!
Hi dave,
How to copy data from columns which are on a different server.
I had to rebuild an Access Database that was kind of a mess. So I started from scratch, I created a database relational database and now I need to take the data from the old database to the new one which is on SQL Server 2005. So I am taking and old MDB and turning it into an ADP and I am trying to find the best way without loosing data, migrating the data from the old to the new. How would I do that??
I was thinking of doing a plain insert, but the thing is the datatypes are not exactly the same
how to multiple rows in the sql 2000through vb6.0 with the concept of RDBMS.
please help me
Its a very helpfull article. Very simple and to the point. Thanks.