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
How will database return LAST_INSERT_ID() in case of 5000 people fired insert query to database at same time
what if using a cursor ?
I am trying to fetch data out of database table and insert into to a table in a different database. Can i insert into table without specifying column name…. by having the address of columns,instead of explicitly specifying the column names in insert query.
If columns order is same then you can. Also there should not be any computed, identity column. What’s the error you are getting?
Hi Pinal, is there a way to make a select from a table in another sql server without using linked server or openrowset? Thanks a lot
I am Facing Problem, regarding Inserting data from One table to
Anothe Table, With Same
Field Strutire, But With Difarent field Position.
Example:
Table 1:
emp1:
Name char 50
Age int
Salary Float
Table 2:
emp2
Name char 50
Salary Float
Age int
insert into emp1 select * from emp2
I cant Insert, Because , Field Order is Diffarent from one Table to
Another Table,
But Both Tables Have Same Data Filed,
Please Let me Know the , The Possible Way to get it done.
Insert into emp1 (Name, Salary, Age) select Name, Salary, Age from emp2
HI Pinal,
i have select statement records[5 rows] which is inserted into a tables. AGAIN running the select query i got 6 rows.How can i insert the 6th record alone into the table nextime. how can i insert only the new\latest data\rows into the table.
in ssis you have sslowly changing dimension companent you can use that one.
Hi…I have tables of A,B,C,D with different column names.I want to get a single table with the columns of A,B,C,D. please suggest a solution.
I use this code :
INSERT INTO
TableIzlez ([BrojNaSmetka],[Artikal],[Sifra],[Kolicina],[Cena_P],[Cena_N],[Prodavac],
[Datum],[Fiskalna],[MasaBroj],[Oddel],[Zatvorena],[PodGrupa],[Cena_Produkti],[TipNaSmetka],[DDV],[Den])
SELECT
BrojNaSmetka,Artikal,Sifra,Kolicina,Cena_P,Cena_N,Prodavac,Datum,
Fiskalna,MasaBroj,Oddel,Zatvorena,PodGrupa,Cena_Produkti,TipNaSmetka,DDV,’19.04.2018′
FROM TableIzlezDneven
After this code I delete the records from TableIzlezDneven ( DELETE FROM TableIzlezDneven) , and next day I use some code to fill the TableIzlez
but sometimes on TableIzlez show double records from TableIzlezDneven .
Why ?
May be sometimes delete didn’t work completely and got rolled-back.
How to copy if the database is in two different sql servers?
HI
My Destination table datatype is different than Source table datatype.
Example: Destination table contains bigint,smallint,char,datetime and varchar datatype where as Source table contains only varchar datatype.
How to insert values from source table to destination table?
Tried this
insert into [dbo].[Invoice Lines XML] select * from [dbo].[Invoice Lines XML 2018Onwards]
but geting below error
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Hi Pinal,
I want to take the daily backup of a single table from LIVE MS SQL server to local ( keeping in mind both are on seperate network with no direct connection in between as of now ). The table will have approx 40 thousand new entry each day. We need to merge those new entries only in our local server on daily basis.
What will be the best approach you suggest?
Thanks in advance,
is this correct to do? I am getting data from the two table joins.
CREATE PROCEDURE [dbo].[sp_Insert_Event](@cid VARCHAR(255))
AS
BEGIN
DECLARE
@did INT
SELECT @did FROM [track].Device AS dev WHERE dev.ControllerId = @cid
–BEGIN TRANSACTION
—-INSERT INTO Destination Table using SELECT
INSERT INTO [track].Event(EventKey, EventName, EventType, EventValue)
SELECT cei.Message, cei.Message, cei.Message, cei.Message
FROM [track].[CustomEventItem] AS cei, [track].[CustomEvent] AS ce
WHERE cei.CustomEventId = ce.CustomEventId AND cei.DeviceId = @did AND ce.Active = ‘true’
—-Verify that Data in Destination Table
SELECT EventKey, EventName, EventType, EventValue
FROM [track].Event
–COMMIT TRANSACTION
END