SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

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.

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: