How to create new tables with "SELECT ... INTO" statements in SQL Server?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "SELECT ... INTO" statement. The tutorial script below gives you a good example:

INSERT INTO tip VALUES (1, 'Learn SQL', 
   'Visit dev.fyicenter.com','2006-07-01')
GO

SELECT * INTO tipBackup FROM tip
GO
(1 rows affected)

SELECT * FROM tipBackup
GO
id  subject    description              create_date
1   Learn SQL  Visit dev.fyicenter.com  2006-07-01

sp_columns tipBackup
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo         tipBackup  id           int       ...
dbo         tipBackup  subject      varchar   ...
dbo         tipBackup  description  varchar   ...
dbo         tipBackup  create_date  datetime  ...

As you can see, the "SELECT ... INTO" statement created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

⇒Managing Tables and Columns in SQL Server

⇒⇒SQL Server Database Tutorials