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

[sharebox5_no_text] [/sharebox5_no_text]

How do you copy a whole table in SQL Server? You use the SQL INTO statement.

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.  You can get the complete syntax at MSDN.

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 it so the data will not come over, but you get the new table with the right column types.

That is how you use the SQL INTO in Microsoft SQL Server

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, google+, etc.

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

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline