560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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.
|
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.