Duplicating (copying) a table in SQL Server database
-
I want to copy a table (not rename - the original table should remain as it is) with all the data in it to another table. Is there any way to do it inside a stored procedure in MS SQL Server? Thanks Thomas George
Highlight the table and select All Tasks -> Export. This will bring you into DTS. Select the Source and then the Destination (in this case they will be the same). Select Copy Table(s), hit next, select the table you want from the source drop-down and then type in a name for the destination table. You can use Transform here to manipulate the fields, etc. Hit Next, you can run this imediately or save in which you can call this DTS Package from a Job, Stored Procedure, etc. (See BOL about calling DTS packages from an sp.) Another way: Select * into (new_table_name) from (old_table_name) This will create a new table with all the info. (NOTE: This 'transfer' is not logged as a transaction, thus can not be rolled back. I believe you can check FULL RECOVERY on you DB and this will correct this.) Good Luck!