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
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?
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
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
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……
Thanks for that post, it solved a long existing problem on our production servers! :-)
Regards
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.
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.
You can use UNION and LIKE operators.
my problem for i have one table (single column) only how i can spread the data store the another table in multiple column ?
Deva – you need to write a select query to split one column into multiple. Then use INSERT…SELECT to achieve whatever you want.
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 ??
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.
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.
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
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.
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 ?