Using SELECT INTO and INSERT to insert SQL rows
Part four of a four-part series of blogs

There are various ways to insert data into new or existing tables in T-SQL - this blog gives examples of each.

  1. Inserting Data into Tables using SQL
  2. Inserting Single Records into a Table
  3. Inserting Multiple Rows of Data into a Table
  4. SELECT INTO - Making a New Table from Existing Rows (this blog)

This blog is part of a full online tutorial in SQL.  Please have a look too at our introductory and advanced SQL training courses for businesses like yours.

Posted by Andy Brown on 12 October 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

SELECT INTO - Making a New Table from Existing Rows

After the complications of inserting rows into an existing table, this is refreshingly simple.  All that you do is take a simple SELECT statment:

-- create table of authors

CREATE TABLE tblAuthor (

AuthorId int IDENTITY(1,1) PRIMARY KEY,

FirstName varchar(100),

LastName varchar(100) NOT NULL,

)

INSERT INTO tblAuthor (

FirstName, LastName

) VALUES (

'Vikram', 'Seth'

)

INSERT INTO tblAuthor (

FirstName, LastName

) VALUES (

'Ian', 'Banks'

)

INSERT INTO tblAuthor (

FirstName, LastName

) VALUES (

'Robert', 'Harris'

)

INSERT INTO tblAuthor (

FirstName, LastName

) VALUES (

'George', 'Orwell'

)

SELECT

LastName,

FirstName,

AuthorId

FROM

tblAuthor

WHERE

LastName <> 'Orwell'

This will return a list of the 3 authors added who aren't called Orwell:

Authors not called Orwell

The authors returned by this SQL.

 

To this, you just add an INTO clause:

SELECT INTO clause

We put the authors returned into a new table.

 

Thus if you amend the end of our SQL to read like this:

-- put authors into new table

SELECT

LastName,

FirstName,

AuthorId

INTO

tblAuthorNew

FROM

tblAuthor

WHERE

LastName <> 'Orwell'

-- show contents of this table

SELECT * FROM tblAuthorNew

 

When you run it, you'll see the same rows returned, but this time from the brand new tblAuthorNew table:

Rows in new table

The same result, but these rows come from a new table.

 

What Happens if the Table Already Exists

If you run the SQL shown above a second time, you'll get an error message:

Error message - table exists

You can't overwrite a table using this method.

Because of this, it can be a good idea to check the table doesn't exist before you attempt to create it from selected rows in an existing table:

BEGIN TRY

DROP TABLE tblAuthorNew

END TRY

 

BEGIN CATCH

END CATCH

 

-- put authors into new table

SELECT

LastName,

FirstName,

AuthorId

INTO

tblAuthorNew

FROM

tblAuthor

WHERE

LastName <> 'Orwell'

-- show contents of this table

SELECT * FROM tblAuthorNew

And those are the ways to insert rows into a new or existing table!

This blog has 0 threads Add post