We're excited to announce that from 14th April we'll be running live online training courses too!
From 14th April we'll be running live online training courses too!
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