Copy a table structure to a new TempDB table

[LucieLastic]

hi All

Is it possible to write sql which automatically creates a temp table in the TempDB database from a table in a normal database? I thought something like this was possible which would auto create and then insert the data or am I getting confused with something else:-

insert into Tempdb.dbo.TempCopy
select * from MyTable

Thanks for any help,
lou

 

[vongrunt]

Why in tempdb?

You can use SELECT * INTO ##TempCopy FROM MyTable to get similar effect.

 

[LucieLastic]

hi

I have replication running of the database and was assuming that any temp tables created in there (using ##) would be replicated and I don't want that. Or does replication not pick up temp tables?

lou

 

[SQLBill]

Is this a table you want to keep? Or is it truly a temporary table?

Remember, TEMPDB is just that - a temporary database. Any time the SQL Server services stop, TEMPDB is dropped. When the services are restarted TEMPDB is created from scratch. So any tables created in it are gone. Some don't think this is a big deal - until their first power outage, or they have to reboot their SQL Server for some reason.

-SQLBill

 

[LucieLastic]

hi SQLBill

I understand the volatility of the TempDB and do actually want temporary tables. The temporary tables will act as transient 'buffers' whilst I check the data before putting the data in the proper (replicated) database. If the SQL server goes down, this is not a problem, as all my database management (ie. create/drop of temp tables etc) is handled by code and the code senses a lost connection and will simply try again later, creating any temporary tables it may need in the process.

I think the best is simply to get my code to run the create table scripts in TempDB for which ever table it wants. I've setup my login to give me Create Table privileges on TempDB so I think this is the best way if not the most elegant.

Any suggestions welcome.

lou

 

[aalmeida]

There is a problem to take in considaration before you start creating tables on TempDb is that it might aquire an exclusive database lock on TempDb. Now the scenario is this:

All transactions are created into TempDb for processing and usualy aquire a row lock but creating tables direct into TempDb usning statements like Select * Into TempDb.Tablename From MyTable will create a database lock that will locks the row level lock wkitch will cause a deadlock in TempDB and you process will be waiting on it self to finish. But since it is a Database lock all other databases in your server will be locked as well forcing you to restart the service.

I would sugest you to create a separate schema in you real database and mark it not for replication until you are done analyzing the data and them move it to the for replication schema.


AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"