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,
i want to know that
how to insert data into 3 tables by one insert query.
pl help me
This is not possible. You need to write three statements
write 3 statements separated with ‘ ; ‘ at the end of each statement
and Execute in EXEC().
Ex.
Exec(‘Insert into tbl1 values(); ‘Insert into tbl2 values(); ‘Insert into tbl3 values();’)
Thanks.
This is a real thing I need but I couldn’t find it anywhere.
Hi all,
i have a problem with this statement:
INSERT INTO buchung_betr(BUDAT)
SELECT BUDAT from Belegdaten
sql server says: invalid column name although the column BUDAT exists in my database.
Please help!
Make sure that column exists in both the tables
Merci beaucoup
hi pinal and madhivaan sir
this time i am doing job in jla_logistics pvt ltd
i am database handler in this company
this company used tally 7.2 i want to say that
tally means maintain full information of company a/c
may i use sql server 2005 in this company
if yes some me give example
company profile -transportation
may use sql server 2005 in this company
hello pinal and madhivaan sir,
First, sorry about my poor english.
If it’s possible, I want to know that:
How can I use the statement insert into table1 (field1,field2,field3,…) select variable1, table2.field2, table2.field3,….. when my table1 has a PK and autoincrement field? (Attention: field1 isn’t the PK and autoincrement field).
When I execute the query returns:
Subquery returned more than 1 value. This is not permitted when the subquery returns follows =, !=, <, , >= or when the subquery as used as an expression.
I’m using SQL Server 2005 Express.
Thanks,
i’m trying to make a procedure based on below instruction but it seems its not working can you help me.
create a parameter that will accept a target database that user wish to saved his output table.
Finally, call the stored procedure in other database aside from master.
Hope you can help me thanks
Hi Sir,
Here My Table like this:
First i’m creating table for Empdetails and defining datatypes and values like this:
Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj int,Salary int,Deptno int)
I’m creating Another Table like EmpInformation
From the above table changing like
“Here Salary datatype is integer but i want change “double” &
Doj datatype convert to DateTime format”
Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj DateTime,Salary Double,Deptno int)
And copy the all records from Empdetails table to new EmpInformation table
It’s Possible r not
plz Reply me
You need to use CAST function
cast(Doj as datetime)
how to create command for datetime double
What did you mean by datetime double?
how to initialize the date and double
hi pinal sir.
plz help me
how to do this.
i have two database
database table
mm emp
mn emp1
database mm hava a table emp and have a data this
id name address
1 mohit delhi
2 mona haridwar
and second database emp is blank
id name address
so i want to data transfer one tabel to another database.how to do this.i am using this
select * into Northwind.dbo.employee from pubs.dbo.employee
but it’s not working
any guy’s plz help me. or send me code
The “Method 2 : SELECT INTO ” is only a school case because none constraints are copied (no primary key, no default, no foreign key).
In the true life, every table has constraints.
I think the best to said is : “never use this method instead script the initial database then use Insert Into ie the Method 1”.
hi, I have to unit five tables into a new table in sql server management studio 2008
Insert into oldtest select * from
(select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip)
can anybody help
It should be
Insert into oldtest
select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip
Hi madhivanan
You suggested query is not working all the tables have same columns and actually I have to extract data from MYSQL server which I extracted in above five tables and now I want to join them in a single table in SQL server management studio 2008
Insert into oldtest
select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip
What did you mean by “it is not working”? Did you get any error
Helpful post, thank you Dave
This was a great help Pinal – Thanks!
Hi,
I need to generate a database script with table values also.. that is table creation + insert values like pubs database..
pls guide
There is an option if you use version 2008
I need to do this in sql server 2005.
Is there s any way..
I need to do this in sql server 2005.
Is there s any way..
Which one are you referring the queries posted in the post or the option to export data along with script?
I have two instance of sql server 2005 in the same box.
When i create recode in table of first instance at same time using trigger i want to transfer that record in another instance of sql 2005.
Waiting for your reply.
This may cause performance issues. One option is to make use of a linked server
How can I insert data in the sql server database table from the access database table???
Use import/export wizard or openrowset