How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
Submitted by: AdministratorLet'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 www.GlobalGuideLine.com','2006-05-01')
GO
SELECT * INTO tipBackup FROM tip
GO
(1 rows affected)
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.globalguideline.com 2008-05-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".
Submitted by: Administrator
INSERT INTO tip VALUES (1, 'Learn SQL',
'Visit www.GlobalGuideLine.com','2006-05-01')
GO
SELECT * INTO tipBackup FROM tip
GO
(1 rows affected)
SELECT * FROM tipBackup
GO
id subject description create_date
1 Learn SQL Visit www.globalguideline.com 2008-05-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".
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
☺ | How To Configure ODBC DSN with Different Port Numbers? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |