Selecting into a Table but Table doesn't Exist? 1

[Phoenix22]

Hi,

When I have the following SQL statement:

SELECT * INTO backup_table FROM orig_table;

The statement executes, but I don't see "backup_table" created anywhere, and when I refer to "backup_table" in another separate SQL query, I get an error message saying the table doesn't exist. Does anyone know how this can be resolved? Thank you in advance for your help.

 

[donutman]

I haven't seen a select into. Usually its:
Insert into BackUP_Table
Select * from Original_Table
-Karl

 

[NathanGriffiths]

Your original code should work!

Check which database(s) you are connected to.

Check you are refreshing your view if looking for the table in QA Object Browser or Enterprise Manager by clicking on a higher node in the menu and pressing F5.

Execute this procedure to make sure SELECT INTO operations are allowed in the database;

sp_dboption 'MyDatabase','select into/bulkcopy','true'

Try using fully qualified table names e.g.

SELECT * INTO MyDatabase.dbo.backup_table 
FROM MyDatabase.dbo.orig_table

SELECT * FROM MyDatabase.dbo.backup_table

 

[donutman]

Tnx SQLGrunt. Does that construct operate any differently than mine? Eg: transaction logs affected, is Backup_Table truncated first?
-Karl

 

[NathanGriffiths]

The difference between using INSERT INTO and SELECT INTO is the INSERT statement requires the table (e.g. 'Backup_table') to exist first, the SELECT does not.

The SELECT statement will create the table if it does not exist, or fail if it does. The structure of the new table will be based on the attributes of the columns in the SELECT query. SELECT INTO can be used to combine columns from several tables in a single table using a JOIN clause.

The effect of this operation on transaction logs depends on the type of Recovery Model implemented. For Simple Recovery, bulk copy and select into operations are not logged, for Bulk-Logged and Full Recovery, select into operations are logged which will have performance implications.

cheers,

Nathan