Duplicate a table in a stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Development Tools

ASP.NET

Duplicate a table in a stored procedure?

Author

Topic

mnish
Starting Member

9 Posts

Posted - 2002-10-24 : 19:40:44

I need to periodically make "snapshots" of a table with a stored procedure. Is there way to merely copy the table--exact structure and data, but only giving it a different name--without manually typing the specific details for the table? Would like to have a dynamic way of doing this so that if the table structure changes, it does not matter.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-24 : 19:44:52

Here ya go! For if you just need the data for the course of the stored proc, the second example is probably what you want.


--Insert into a non temp table
SELECT *
INTO myNewTable
FROM MySourceTable
WHERE key=@key 


--Insert into a Temp Table
SELECT *
INTO #myNewTable
FROM MySourceTable
WHERE key=@key

Michael

<edit>Woohoo, beat Rob by about 30 seconds!</edit>

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 10/24/2002 19:47:18

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-24 : 19:45:28

SELECT * INTO NewTableName FROM OriginalTable

Books Online has more details on "SELECT...INTO". Also look at "temp tables", if this is only a snapshot, a temp table is ideal. Temp tables are preceded with a # symbol:

SELECT * INTO #NewTableName FROM OriginalTable

mnish
Starting Member

9 Posts

Posted - 2002-10-24 : 20:44:59

I guess I should have been more precise. I actually need to create a new table that is an exact duplicate of an existing table. This will be done on a daily basis and the new tables need to be saved as a normal--not temp--table. Will put date stamps in the new tables' names to distinguish them. This is basically making a backup table each day (hey, it is what the customer wants!). It is easy to insert into a table--but how do I actually CREATE a new duplicate table on the fly without listing all the columns and details. Seems like SQL Server should have some nifty "copy and paste" sort functionality that I can use.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-24 : 20:58:35

Uhhhh, that's EXACTLY what SELECT...INTO will do, except it won't copy indexes or constraints. Since these are backup tables that's not much of a problem IMHO.

And you NEED to talk your customer out of doing this. They will end up with hundreds of tables very quickly and will go through hell trying to get data out of them. Guess who they'll blame for that?

mnish
Starting Member

9 Posts

Posted - 2002-10-24 : 21:01:34

INSERT INTO will create the table structure from the existing table AND copy the data over? Really? That is swell.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-24 : 21:07:45

NO, SELECT...INTO. INSERT needs an existing table structure. SELECT...INTO creates the table on the fly from the structure of the columns returned in the SELECT clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources