This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Inserting Data into Tables using SQL Inserting Single Records into a Table Inserting Multiple Rows of Data into a Table 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.

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.

Inserting Data into Tables using SQL
Inserting Single Records into a Table
Inserting Multiple Rows of Data into a Table
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

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:

The authors returned by this SQL.

To this, you just add an 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:

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:

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!

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

SELECT INTO - Making a New Table from Existing Rows

What Happens if the Table Already Exists

Head office

London

Manchester