Tuesday, June 28, 2011

Insert Data From One Table to Another Table

There are many ways of inserting data from one table to another table through Query, here I am explaining two options. 

Option 1 : INSERT INTO SELECT 
The below query is used when table is already exist in the database earlier and only data have to be inserted into this table from another table. You can insert insert entire table using '*' or giving selected column names. 

Here is the script to insert only selected column's not all columns to Target Table.
USE AdventureWorks 
GO
INSERT INTO TargetTable (FirstName, LastName, EmpName)
SELECT FirstName, LastName, EmpName 
FROM dbo.SourceTable 
GO

Here is the script to insert all column's from source using * to Target Table. 
USE AdventureWorks 
GO
INSERT INTO TargetTable 
SELECT * FROM dbo.SourceTable 
GO 

Here is the script to verify data in Target Table. 
USE AdventureWorks 
GO
SELECT * FROM TargetTable 
GO 

Note: if the table identity is on then it doesn't work. you have to do some tricks like identity on/off through GUI. 

Option 2 : SELECT INTO 
The below query is used when there is no table exist. New table is created with same data types as selected columns but you have to creates keys manually. 

Here is the script to insert only selected column's not all columns to Target Table. 
USE AdventureWorks 
GO
SELECT TargetTable (FirstName, LastName, EmpName)
INTO TragetedTable 
FROM dbo.SourceTable 
GO 

Here is the script to insert all column's from source using * to Target Table. 
USE AdventureWorks 
GO
SELECT TargetTable 
INTO
FROM dbo.SourceTable 
GO 

Here is the script to verify data in Target Table. 
USE AdventureWorks 
GO
SELECT * FROM TargetTable 
GO
Insert Data From One Table to Another Table. SQL INSERT INTO Statement. transfer (insert) data from one table to another table. import and export table data. table backup in slq server 2008. table restore in sql server 2008. Transferring Data from One Table to Another. dumping data from one table to another table. How to do INSERT into a table records extracted from another. Using SELECT to INSERT records. insert the value of another table. into the current table. insert into one table all rows from another table. inserting data from one table to another table in sql server. inserting data from one table to another in sql. inserting data from one database to another. inserting multiple rows in sql. SQL SELECT INTO Statement. Copy data from one table to another including identity column values. insert data from one table another and avoid duplicate insertions.

0 comments:

Post a Comment