Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE


How can we insert data from one table to another table without using import/export wizard?

How will you create one table (new table) from another table (source table). The new table should be empty after creation?

Well, there are two different methods to perform inserting data from one table to another table. I prefer to use second method as performance of this methods is good.

Method 1 : SELECT INTO
In this method, new table is created with same data types as selected columns. This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table.


USE testdb
GO
--Create new table and insert into table using SELECT INSERT
SELECT column1, column2
INTO dest_table
FROM testdb.source_table

----Verify that Data in destination_Table
SELECT column1, column2
FROM dest_table

You may also try below


SELECT *
INTO destination_table_name .dbo.externaldatabase
FROM source_table_name

Method 2 : INSERT INTO SELECT
To use this method you will have to create a target table first with similar table definition without taking any record.


CREATE TABLE targettablename [(column1,column2)] AS
SELECT column1,column2 FROM source_tablename WHERE 1 = 2

Now the data is to be inserted into this table from another table. To enter data from one table to another use this..


USE test_database
GO

----INSERT INTO dest_Table using SELECT
INSERT INTO target_tablename (column1, column2)
SELECT column1, column2
FROM db_name.source_tablename
WHERE countrytype = 1

----Verify that Data exist in targetTableName
SELECT column1, column2
FROM target_tablename
----Clean Up Database
DROP TABLE target_tablename
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: