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
http://www.sqltrainingonline.com/

YOUTUBE NEWS UPDATES
   / sqltrainingonline  

VISIT SQLTRAININGONLINE.COM FOR MORE VIDEO NEWS & TIPS
http://www.sqltrainingonline.com

SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!
http://www.youtube.com/subscription_c...

SUBSCRIBE TO OUR EMAIL LIST!
http://www.sqltrainingonline.com

LET'S CONNECT!
Facebook:   / sqltrainingonline  
Twitter:   / sql_by_joey  
Linkedin:   / joeyblue  
SQLTrainingOnline: http://www.sqltrainingonline.com