SQL Into - How to Copy Table Data with Select Into Statement - Quick Tips Ep37

SQL Into - How to Copy Table Data with Select Into Statement - SQL Training Online

In this video, I show you how to copy data using the SQL INTO statement. Including a trick to copy only a table schema in SQL Server.

TABLE OF CONTENTS
00:00 - Intro
01:04 - Select INTO Example
01:58 - Delete Example
02:06 - Insert INTO Select Example
03:50 - Create an Empty table with Select INTO where 1=2

DESCRIPTION
How do you copy a whole table in SQL Server?

You often see the SQL INTO statement when you are using INSERT, but there is a special INTO statement in SQL Server that is used with the SELECT statement that allows you to make a copy of a table for development, testing, or whatever purpose you can think of.

So, to start, let's take a look at the SQL Training Online Simple DB employee table.

You can see that we have 14 records

Now, all we have to do to make a copy of the table is to simply add the SQL INTO clause:

SELECT *
INTO EMPLOYEE_TEST
FROM EMPLOYEE

If we run that, you see that we made a copy of the table with all of the data.

Now, I want to go ahead and empty the employee_test table and show you how to use the INSERT INTO.

If you already have a copy of the table, (I will show you a shortcut in a minute on how to create a copy without data.) then you can use the following insert into statement:

INSERT INTO EMPLOYEE_TEST
SELECT *
FROM EMPLOYEE

After running that statement, you see that I have inserted data from the Employee table into the employee_test table. This is sometimes referred to as INSERT INTO SELECT.
But, there is one trick that I have used throughout the years that is pretty cool.

Sometimes, you want the table schema, but you don't want the data. Now, I know that you can use Management Studio to script out the table for you, but there is an easier way that is more flexible as far as I am concerned.

That is to use the SELECT INTO FROM, but put the following in the WHERE clause:

SELECT *
INTO EMPLOYEE_TEST
FROM EMPLOYEE
WHERE 1=2

The 1=2 makes is so not data will come over, but you get the new table with the right column types.
Is that pretty cool? I have found many uses for this over the years. Let me know what you think bellow.

Let me know what you think by commenting or sharing on twitter, facebook, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

READ THE ORIGINAL ARTICLE WITH SQL SCRIPTS HERE

YOUTUBE NEWS UPDATES

VISIT SQLTRAININGONLINE.COM FOR MORE VIDEO NEWS & TIPS

SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!

SUBSCRIBE TO OUR EMAIL LIST!

LET'S CONNECT!

[tahirAbbasBokhari]

Excellent - precise and perfect explanation.. Thank you

[rakesh-pirh]

Great explanation....and good info too

[vitorfernandes-neto]

excellent video, not to fast, just perfect

[barrycantrell]

Good video Joey. This was very helpful.

[cesarreinoso]

Joey great job, thanks for the video quite insightful

[agustinaguayo]

When you update the copy does it update the original as well?

[krishbrand]

made it look easy... I have a doubt
Will all the MS SQL statements will work in Oracle...

[annayayloyan]

Hi Joey. I want to create a new table that includes a join of two other tables. I am sure its possible, but I don't know how to create the columns of the joined table first. the (when 1=2 doesn't work since I am not copying an existing table.. As you can tell I am new to SQL

[MustafaAmed]

Greate explanation but im wondering if it could work for SQLite.

[chrisjustinvincentnuay]

Undeclared Variable: Im using XAMPP. What to do?

[pamelapricesoullinedancing]

If I copy into a file (into outfile 'spool.txt') where do I find the file that is created?

[kalpatarunayak]

how to copy one column n data to another existing table

[moesadr]

How can we only duplicate a table's format and structure (without date). That can be used as template for creating additional tables???

[rickhorvath]

No, how do you copy into the SAME table, with new unique ids?