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