can't drop table

[ksbigfoot]

I am using SQL Server 8.0.
I have a corrupted table in my Production database.
I have a copy in my test database.
I don't know how to copy the exact table from test back into Production.

I tried to Drop table Table1
I get an error message "Could not drop object 'Table1' because it is referenced by a FOREIGN KEY constraint.
Does anyone know a way around this?

 

[ksbigfoot]

I actually don't want to drop the table.
I would like to do one of 2 things.
1.) Append all data from DatabaseTest table1 to DatabaseProd table1 where the ID fields are different.
2.) Remove all data from DatabaseProd table1 and then copy over all data from DatabaseTest table1.

#2 is my preferred way of doing things. Does anyone know if I can do this?

Thanks

 

[mutley1]

Hey Bigfoot,

Depends on setup - are the 2 databases on the same server or different servers?

If they are on the same server, the easiest thing to do is

truncate table databaseprod..table1
GO
insert into databaseprod..table1
select * from databasetest..table1

assuming the tables have exactly the same structure.


HTH

M.

 

[ksbigfoot]

Howdy mutley1,

Both my databases (Prod & Test) are on the same server.
I haven't tried your code yet, but do I really put two periods? Also what does the word GO do?
I will have to do some research before I run the code you suggested.

I am assuming that I should run the following code from Query Analyzer?

truncate table databaseprod..table1
GO
insert into databaseprod..table1
select * from databasetest..table1

Thanks again,
ksbigfoot

 

[mutley1]

Hey Bigfoot,

yes.....2 periods = database.dbo.mytable and yes, run it in QA. I can't remember off the top of my head if you need the GO in the code, but it doesn't harm anything, put it like that (just means move on to the next statement in the same block of code)!

If the databases are on the same server then you can also use an import with DTS. Depends if you want to run it regularly.

I would also check out why the table got corrupt. If it is a long standing issue then you might need to find out why it went pear shaped...

Cheers,

M

 

[ksbigfoot]

Howdy mutley1,
The database table was corrupted because another developer wrote some code and moved it into an application that a user ran and it blew away 200,000 records out of the table.
Can't really complain as the other developer is a partner at the business I contract at

I will try your code out on monday and I will let you know how it goes.

thanks again,
ksbigfoot.

 

[mutley1]

Developers....don't you just love them!

Good luck - hope it works for you.

Cheers,

M.

 

[mutley1]

Hey Bigfoot,

Sorry, forgot 1 thing. It sounds like you might also have an identity column on the table (whenever you insert etc. it assigns a number to the specific row (check out identity seed in BOL), which means you may need to turn the identity insert when doing the update statement. Slight change to the code in this case. You need to specify all the fields in the destination table:

--Clear the destination table
TRUNCATE TABLE databaseprod..table1
--Set the identity insert
SET IDENTITY_INSERT databaseprod..table1 ON
--insert specifying ALL fields in the table
INSERT INTO databaseprod..table1 (field1, field2, field3, field4....etc.etc.etc) 
--No need to specify the source fields because you have set the destination fields in the exact order above, hopefully!
SELECT * 
FROM databasetest..table1

The above assumes the source and destination are exactly the same structure - you need to specify all fields in the INSERT INTO clause. If you do it in Query Analyser you can expand the object tree down to the particular table until you see "columns" and then drag that between the brackets and it populates automatically.

HTH

M.