Script Table without losing data

[RSfromCO]

I would like to generate a SQL script using my current table format, which when executed would drop the table, re-build it using the updated structure, and then reload the table with data that was already in the table (assuming that the new structure still contains all of the old fields). Is there an easy way to do this? When I select Generate SQL Script, Enterprise Manager creates a script that drops the table and re-builds it but all the old data is then lost.

 

[manmaria]

select *
into newtable
from oldtable;

drop oldtable

 

[Catadmin]

Just an FYI: the Select...Into statement does NOT copy over any indexes, triggers, foreign or primary key references. It'll copy over data only. So if you need those connections, you'll have to recreate them after you do your Select...Into statement and before you drop your old table.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"