Insert or Copy Data into Table

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.

Insert Data into table with two columns, one is Identity column and another is RowVersion

This post is from a discussion in one of our SQL Server training session with Mr. Rakesh Mishra.  While discussing RowVersion data type, I have created a table with two columns one is of INT IDENTITY and another is of ROWVERSION type. Now the situations arises is how to insert data into that table?

Note:  ROWVERSION which is auto generated, unique binary number within a database and generally used for version stamping in table rows.

In the ROWVERSION column we can’t explicitly insert data, so can we insert data into the INDENTITY column without making the IDENTITY_INSERT to ON?

Yes, we can do that and here is the way it can be done.

--Create the table for demo
CREATE TABLE RowVersionTest(myID INT IDENTITY PRIMARY KEY, myRowVer ROWVERSION)

What if I will insert data into the IDENTITY column?

--Insert Data
INSERT INTO RowVersionTest(myID) VALUES (1)

MESSAGE:
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'RowVersionTest' when 
IDENTITY_INSERT is set to OFF.

The above error message is because, we have not we can’t insert data into a IDENTITY column till IDENTITY_INSERT is OFF.  So here is the solution for this:

--Insert Data
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
--Select Data
SELECT * FROM #RowVersionTest

--Cleanup

DROP TABLE #RowVersionTest

Please leave your valuable comments.