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 bubu,
I hope this will help u to solve ur problem.
Suppose Test1 is a table in that there are 3 columns(CL1,Cl2,Cl3) so now we are adding the CL1 & CL2 into CL3
So below procedure ma help u.
Create Proc Insert2RecordsInTo1Col
AS
Insert into Test1(Cl3)
Select Cl3= CL1+Cl2 from Test2
GO
I want to insert records from more than one table into a new table..
can anyone help??
thanks in advance !!!!
Hi Punter,
I hope the below query will help u.
There are 3 tables(Test1, Test2, Test3) and the Test1&Test2 are named as T1, T2
In the Test1 we have First_Name(Column)
In the Test2 we have Last_Name(Column)
In the Test3 we have Full Name(Column)
So now i am inserting Test3 table in the Full_Name(Column)
from Test1&Test2 tables “First_Name+Last_Name”
but i have a comon column ‘ID’ in both the tables(i.e,T1, T2)
so i am comparing the ‘ID’ column from both tables and inserting it in the Test3 table in the Full_Name Column.
Insert into Test3(Full_Name)
Select T1.First_Name+’ ‘+T2.Last_Name from
Test1 T1, Test2 T2
where T1.ID = T2.Id
Hi
Thanks for the post. Helped me a lot. Seems to have generated a few questions though…
Thanks
Paul
hi,
I need a help .. need to insert a purticular field value into another table.(need trigger)
cons:
T1 = table one
f1=field1
t2=table two
f2=field2
then have to insert using trigger
update t1 set f1=t2.f2
please help me
list advanced t-sql querying methods
hi.please help me.. i want to insert date to my table in sqldeveloper,but i cannot.. what i must do can anyone write this code whole!!!!
HI,
i have a probelem in sqlserver 2005.
i want to integrate data into a single intergration table from many other tables.
all tables have some column names. and i want to bring the data such that if a entry in row is changed it should be updated , and if row doesnot exist is should be inserted.
But probelem here is i have to compare 7-8 columns in a single row and then decide to update or not is there any solution.
there are a lot of solution available but they compare 1 or 2 columns in a row only.
PINAL please help out.
Please can someone help me…
I am fairly new in the DB domain and I need to create a scripte that will CUT data from a table and PASTE it to a new table. The objective is to segragate historical data per month to improve the performance of a reporting tool.
Also how can I implement variables into my script to let the user specify from what date till what date he want to “transfer” the data into a new table that will have a variable name as well?
Is this possible?
Thank you in advance for any help!
Hi
Looks the situation you have best suits for table partitioning.
The Data movement will also be very fast when partioning is in place . the requirement will be just to have a column in the table with the representing time or date which will help us to segregate data very easily.
Regards
Muralidharan
So I am having a problem. I have set up my database tables in phpMyAdmin. Therefore, in my php code I am having a hard time writing the code to insert a primary key from one table into another. For example,
StudentInfo (stdID, FName, etc)
LookUpInfo(stdID, ProductID, etc)
If anyone understand please HHEEELLPPP.
$query= “INSERT INTO LookUpInfo(stdID) VALUES (‘stdID’)”;
$result= $result = mysql_query($query)
or die (“Query failed: ” . mysql_error());
DOESNT WORK…PLLLEEAASSEEE HEELLLPP!!!!
Hi
PL help me, i want export data through query in SQL
The following query helps to create the archival of table and dump the data from source table into test table based on criteria.
Step one: Run the following query
SELECT * INTO tbltest FROM tblsorce WHERE year(fieldyear) >= 2008
step Two: Refresh the table list
You will see a new table tbltest
Then use this table and test your query.
sql for two different table from two different server
like server1 & server2 — Two Different Server Not Database
tables table1, table2
select a.sn, b.name from server1.table1 left a join server2.table2 b on a.sn=b.sn
This is the query for inserting data form one table to another table in different DATABASE
THIS IS A SUCCESSFUL QUERY
insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,’Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup
how i can insert data from one server database table
to another server database table through sql query.
Read about sp_addLinkedServer in SQL Server help file
This is the query for inserting data form one table to another table in different DATABASE
THIS IS A SUCCESSFUL QUERY
insert into DocTypeGroup
Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
from Opendatasource( ‘SQLOLEDB’,’Data Source=10.132.20.19;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup
Thank you, this was exactly the procedure I was looking for and worked perfectly for me!
Good job!
Hi,
I am connected to a network server through sql server management studio express.
I need to restore a copy of the backup which resides in my pc into the db connected through the network.
When I click on the restore db thru files option, I am able to browse thru only the drives/files of the server. How do I restore the db from my pc?
Thanks,
Priya.
Hi frnds,
i m try to do one query.
i have two tables
M_Payment (machine no, total amt, reference no)
Mode_of_payment (reference no, cash amt, cheq amt, DD amd)
data are likn:-
M_Payment
machine no total amt reference no consumer No
1 1000 122 C1
1 200 123 C2
1 300 124 C3
2 500 125 C4
2 500 125 C5
Mode_Of_Payment
reference no cash amt cheq amt DD amt
122 500
122 500
123 200
124 100
124 200
125 500
125 250
125 250
i want machine no wise total amt and sum of total cash amt, total chq amt and total DD amt
my require o/ p is…
O/P
machine no totla amt cash amt cheq amt DD amt
1 1500 600 700 200
2 1000 500 250 250
PLZ ANY BODY HAVE SOLUTION FOR THIS TYPE OF OUT PUT. I HAD TRY LOTS OF SELECT QUERY BUT I CANT GET THIS TYPE IS OUT PUT.
Kruti
Thanks
Hi Pinal,
Wish you and your Family a Very Happy New Year 2009 :-)
Pinal i have my Group on Yahoo for MSSqlServer which is “ASKSQLEXPERTS”, its my Pleasure if you will Join this Group so that all the members of this Group will get more help…
Thanks & Regards
Dharmendra Dixit