Using Temp table in job steps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Using Temp table in job steps

Author

Topic

CanadaDBA

583 Posts

Posted - 2009-02-20 : 13:02:43

I restore production database into a Dev machine every night but need to keep some tables information in the Dev database and prevent them to be overwritten.

I have implemented this by creating these tables in TempDB and after the restore is complete then move the data back to the tables.

1. Job step 1:
SELECT * INTO [TempDB..T1] FROM [MyDB].[dbo].[T1]

2. Job step 2:
RESTORE into MyDB from production backup

3. Job step 3:
TRUNCATE TABLE [MyDB].[dbo].[T1]
INSERT INTO [MyDB].[dbo].[T1] SELECT * FROM [TempDB..T1]
DROP TABLE [TempDB..T1]

I was trying to use temp table concept but it seems as soon as a step is complete, the #table (or ##table) disappears. Is it possible to use Temporary table Or variable table at all?

Canada DBA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 13:15:46

Temp Table will be out of scope . Try with ##Temptable(Global).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:16:28

you can take a db snapshot of dev db before restoration and then copy the reqd tables after restoration back into db from snapshot.

http://www.sqlservercentral.com/articles/Administration/2733/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 13:20:07

quote:
Originally posted by visakh16

you can take a db snapshot of dev db before restoration and then copy the reqd tables after restoration back into db from snapshot.

http://www.sqlservercentral.com/articles/Administration/2733/

This won't work as Database snapshot is useless when primary database is offline during restore with Overwrite.

CanadaDBA

583 Posts

Posted - 2009-02-23 : 09:01:25

This is weird. This command

SELECT * INTO [TempDB..T1] FROM [MyDB].[dbo].[T1]

Creates a table in TEMPDB as “TempDB..T1”!! This is the table name not the full qualified name for the table.

Even if I change the table creation to

SELECT * INTO [TempDB.dbo.T1] FROM [MyDB].[dbo].[T1]

It creates a table in TEMPDB as “TempDB.dbo.T1”!! Again this is NOT the full qualified table name. It is the table name itself.

And worse, if I run both lines:


SELECT * INTO [TempDB..T1] FROM [MyDB].[dbo].[T1]
SELECT * INTO [TempDB.dbo.T1] FROM [MyDB].[dbo].[T1]

Then two tables with these names TempDB..T1 and TempDB.dbo.T1 are being created in TEMPDB. Why is this behavior?

Canada DBA

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 09:07:57

Cos you have the name between []s.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 09:09:03

When you run,

SELECT * INTO [TempDB..T1] FROM [MyDB].[dbo].[T1]
SELECT * INTO [TempDB.dbo.T1] FROM [MyDB].[dbo].[T1]

The name is taken as what you specified between []s.
Change it to,

SELECT * INTO TempDB..[T1] FROM [MyDB].[dbo].[T1]
SELECT * INTO TempDB.dbo.[T1] FROM [MyDB].[dbo].[T1]

CanadaDBA

583 Posts

Posted - 2009-02-23 : 09:10:07

This is what I am doing. Notice the select statements. I have used [] everywhere.

Canada DBA

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 09:11:51

Also, The 2 tables you created there won't be in tempdb, but in the same db where from you are running them. Thats unless if you are running them in tempdb only.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 09:13:13