Using Oracle, a table can be created with any valid SELECT command. Microsoft SQL Server provides the same functionality with different syntax.
| Oracle | Microsoft SQL Server |
|---|---|
| CREATE TABLE STUDENTBACKUP AS SELECT * FROM STUDENT | SELECT * INTO STUDENTBACKUP FROM STUDENT |
SELECT…INTO does not work unless the database to which this is applied has the database configuration option select into/bulkcopy set to true. (The database owner can set this option using SQL Server Enterprise Manager or the Transact-SQL sp_dboption system stored procedure.) Use the sp_helpdb system stored procedure to check the status of the database. If select into/bulkcopy is not set to true, you can still use a SELECT statement to copy into a temporary table:
SELECT * INTO #student_backup FROM user_db.student_admin.student
When new tables are created using SELECT.. INTO statements, referential integrity definitions are not transferred to the new table.
The need to have the select into/bulkcopy option set to true may complicate the migration process. If you must copy data into tables by using a SELECT statement, create the table first, and then use the INSERT INTO…SELECT statement to load the table. The syntax is the same for Oracle and SQL Server, and does not require that any database option be set.