Insert or Copy Data into Table
Posted by Prashant on June 11, 2010
This post is for a common situation most of us face while working with a database, which is copy/insert to a table. We can use INSERT & SELECT to copy data into a database table in three ways.
- INSERT Statement (Direct insert data values into the table):
Using INSERT statement we can add one data row at a time to destination table.
--Example: 1 CREATE TABLE Table01 (Column1 INT PRIMARY KEY, Column2 varchar(10)) GO INSERT INTO Table01(Column1,Column2) VALUES(101,'A') INSERT INTO Table01(Column1,Column2) VALUES(102,'B') INSERT INTO Table01(Column1,Column2) VALUES(103,'C') INSERT INTO Table01(Column1) VALUES(104) GO SELECT Column1,Column2 FROM Table01 GO
- INSERT Statement with a SELECT Sub-Query:
If the destination table (we want to populate) is already created in the database, then using INSERT statement with a SELECT sub-query, we can copy data rows from one table to another. The number of columns listed in INSERT statement must be same as the columns listed in SELECT statement and their data types must match.
--Example: 2 CREATE TABLE Table02 (Column1 INT IDENTITY, Column2 VARCHAR(10), Column3 VARCHAR(8) DEFAULT('Welcome1')) GO INSERT INTO Table02(Column2) SELECT Column2 FROM Table01 GO SELECT Column1, Column2,Column3 FROM Table02 GO
- SELECT with INTO Statement:
If the destination table (we want to populate) is not available in the database, then using SELECT INTO statement also it will create the table on the fly and copy data to it from the source table.
--Example: 3 SELECT Column1, Column2, Column3 INTO NewTable FROM Table02 WHERE Column2 IS NOT NULL GO SELECT * FROM NewTable GO
Please let me know your valuable comments.