SQL drop table and re-create and keep data

sql sql-server foreign-keys sql-drop
user516883 picture user516883 · May 17, 2011 · Viewed 53.5k times · Source

On our original design we screwed up a foreign key constraint in our table. Now that the table is full of data we cannot change it without dropping all of the records in the table. The only solution I could think of is to create a backup table and put all of the records in there, then delete all the records, alter the table and start adding them back. Any other (BETTER) ideas? Thanks!

Using MS SQL Server

Answer

voodoo_patch picture voodoo_patch · Aug 2, 2017

I'm a bit late, just for reference.
If You are using SQL Server Management Studio, You could generate a DROP and RECREATE script with "Keep schema and data" option.

  1. Right click on the desired DB in object explorer
  2. Tasks > Generate scripts
  3. Select the table you want to script
  4. Then clicking on Advanced button
    • "Script DROP and CREATE" ->"Script DROP and CREATE"
    • "Types of data to script" -> "Schema and data"

Hope this helps

Related questions

INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

I am getting the following error. Could you please help me? Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_…

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

I have a problem when trying to add a foreign key to my tblDomare table; what am I doing wrong here? CREATE TABLE tblDomare (PersNR VARCHAR (15) NOT NULL, fNamn VARCHAR (15) NOT NULL, eNamn VARCHAR (20) NOT NULL, Erfarenhet VARCHAR (5), PRIMARY KEY (…

Read More