November 15, 2010 at 2:15 pm
I was just given a db that has duplicate records in several tables. My method is to select distinct into a new table then rename old table to new name, and new table to old name. I've run each of the steps below independently and they work fine. I was going to make an sp to drop the new tables if they were already there, then select distinct * into them, and do the renaming. single routine instead of multiple manual actions.
here is the basic structure for a single table sp:
CREATE PROCEDURE [dbo].[ztest] AS
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[batch_unique]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
drop table [dbo].[batch_unique]
GO
SELECT DISTINCT BATCHINFO.* INTO batch_unique
FROM BATCHINFO
GO
when I try to save that the first time the system stalls for about 30 sec, then saves, but when I reopen the sp it only has the drop table statement in it. I add the select statement again and try to save and it tells me the table is already there - I thought the Drop table at the beginning was supposed to take care of that? I run the drop table by itself successfully, but then start the same edit & save process that I'd just gone through with the same results.
anybody know what rule I'm violating with this?
thanks in advance.
November 15, 2010 at 2:29 pm
Remove the GO after the Drop Table statement. It ends the batch.
If you want an in-proc terminator, use ;
It's not necessary here though.
Go forces the following script to happen no matter what errors occured in the one before it. It terminates any open commands, such as a CREATE.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 2:42 pm
by golly, that seems to have worked. I thought the go would force the drop to complete before moving on.
THANKS!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply