SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

October 24, 2012 by Joey Blue Leave a Comment

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

Video: 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.

[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.

SQL INTO - SELECT INTO FROM

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

Related

Filed Under: SQL Training Tagged With: SQL INTO

Leave a Question, Comment, or Reply. All are welcome!Cancel reply

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com