Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "SELECT ... INTO" - Creating New Tables With Queries

By: FYIcenter.com

(Continued from previous topic...)

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

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

(Continued on next topic...)

1. What is a table?
2. What are DDL (Data Definition Language) statements for tables?
3. How to create new tables with "CREATE TABLE" statements?
4. How To Get a List of All Tables with "sys.tables" View?
5. How To Get a List of Columns using the "sys.columns" View?
6. How To Get a List of Columns using the "sp_columns" Stored Procedure?
7. How To Get a List of Columns using the "sp_help" Stored Procedure?
8. How To Generate CREATE TABLE Script on an Existing Table?
9. How to create new tables with "SELECT ... INTO" statements?
10. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
11. How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
12. How to rename an existing column with the "sp_rename" stored procedure?
13. How to rename an existing column with SQL Server Management Studio?
14. How to change the data type of an existing column with "ALTER TABLE" statements?
15. How to rename an existing table with the "sp_rename" stored procedure?
16. How To Drop an Existing Table with "DROP TABLE" Statements?