Drop & create new table

Question

Post reply

Drop & create new table

neddyflanders

SSC Eights!

Points: 972
More actions
February 1, 2005 at 12:28 pm

#74853

Hello, I have a table that I want to drop and recreate along with all of it's data. How would I go about this, using script. Please don't ask why I want to do this, I just have to. Any help would be appreciated, I'm fairly new to SQL. I won't even ask how I bring the constraints and indexes over at this point and also please don't say use EM, it all must be script. thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Because I don't know the purpose of this. This is what I would recommend:

--Drop it

select * into TableName_bk from TblName

DROP Table TblName

-- Recreate it

Select * into TableName from TblName_Bk

DROP TABLE TblName_Bk

Well?

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Go to Enterprise Manager and open the database you want. Tables will be one of the icons. Click on that and on the right-hand side of the screen you will see all the tables in the database.

Scroll to find yours. Right click on your highlighted table and scroll down to "All Tasks". Then choose "Generate SQL Script". You will get a new window with 3 tabs. On the formatting tab, you can choose everything but "Only Script 7.0 Compatible Features". On the Options tab, you can choose many of the Table Scripting Options.

Run this and save it somewhere. This is the start of your script. Open this new script in SQL Analyser and change the name of the table to something else. After the script portion, do a INSERT INTO NewTable SELECT * FROM OldTable. Then you can DROP TABLE OldTable. You will need to look up in Books OnLine (BOL) how to rename the table within the script. Don't know that off the top of my head.

Hope this helps.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

To rename the table :

exec sp_rename 'CurrentName', 'NewName', 'OBJECT'

neddyflanders SSC Eights! Points: 972 More actions · Answer 4

Thanks everybody. NoelD, that script worked fine. I appreciate it. ned.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ok now that we've helped you can you tell us why you needed to do that?

neddyflanders SSC Eights! Points: 972 More actions · Answer 6

Just for my own information. I'm new to T-SQL and have been using alters to change columns etc. Have been told sometimes you might want to drop and recreate a column instead. So thought I might as well find out how. thanks everybody.

deepankar.bhardwaj SSC Enthusiast Points: 161 More actions · Answer 7

Mate !!!

I did tried to do the same but seems this query is not working...

deepankar.bhardwaj SSC Enthusiast Points: 161 More actions · Answer 8

In order to create ,drop and recreate same table in same session we have to use GO statement thus to break the script into smaller chunks then to kick this in one session:

create table #Deep1 (id int)

insert into #Deep1 1

insert into #Deep1 2

select * into #Deep_backup from #Deep1

go

drop table #Deep1

go

select into #Deep1 from #Deep_backup

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

neddyflanders (2/1/2005)
Just for my own information. I'm new to T-SQL and have been using alters to change columns etc. Have been told sometimes you might want to drop and recreate a column instead. So thought I might as well find out how. thanks everybody.<img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>

What error are you getting?

What query did you use?