BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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!
- 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)