[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Recreate table and contents with T-SQL

I need to copy a tables contents to a temporary table and then drop, recreate and repopulate the original table. I need to do this for a lot of tables so a generic solution would be ideal i.e. one where I only have to edit the table name.
0
Blowfelt82
Asked:
Blowfelt82
2 Solutions
 
Pratima PharandeCommented:
try the procedure like this

CREATE PROCEDURE Copy_data_Drop
@TableName VarChar
AS

DECLARE @sql varchar(2000)
DECLARE @temptable varchar(50)

Set @temptable = 'tmp' + @TableName

SET @sql = 'SELECT * INTO ' + @temptable + ' from ' + @TableName

EXEC (@sql)

SET @sql = 'Drop table '  + @TableName

EXEC (@sql)

SET @sql = 'SELECT * INTO ' + @TableName + ' from ' + @temptable

EXEC (@sql)
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

If you wanted to re-create same table with same table design then Pratima showed you right way.
but between, why you wanted to do that?
secondly, It wont create any keys (Primary & Foreign Keys)

whats your exact requirement?


- Bhavesh
0
 
sachinpatil10dCommented:
Try this.

RecrateTable store procedure which takes table name as input
and uses CreatTable function to create table with constraints
CreateTable.sql
RecreateTable.sql
0
 
Blowfelt82Author Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now