Identity Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Identity Key

Author

Topic

mpaulopes
Starting Member

1 Post

Posted - 2005-10-27 : 14:14:56

hi everybody,

I created a DTS pack. to create a table, copy values into it from 2 other tables rename the current table in production, rename the created table and drop the old renamed table.

I'm having some trouble creating the Identity in the new table.

here is my code

Creates temp table:

if exists (select * from dbo.something where id = object_id(N'[user].[temp_inventory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
truncate table temp_inventory
end
else
begin
CREATE TABLE [ColdFusion].[temp_inventory] (
[ITEMNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITEMID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONFIGID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UNITID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QTY] [decimal](28, 12) NOT NULL ,
) ON [PRIMARY]
end
__________________________________________

Appends with a production table

if exists (select * from dbo.something where id = object_id(N'[user].[temp_products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table temp_products
end

SELECT * INTO temp_products FROM products

SET NOCOUNT ON
--DEALLOCATE prod_cursor
DECLARE @qty numeric(13), @ITEMID VARCHAR(20), @CONFIGID VARCHAR(25),@thisConfig INT
DECLARE prod_cursor CURSOR FOR
SELECT qty,itemid,configid
FROM temp_inventory
--WHERE processed <> 1
ORDER BY itemID asc, configID asc

OPEN prod_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM prod_cursor INTO @qty, @ITEMID, @CONFIGID
if @@fetch_status <> 0
break

UPDATE temp_products
SET quantity = @qty
WHERE axaptaID = @ITEMID
AND config = @CONFIGID
END
CLOSE prod_cursor
DEALLOCATE prod_cursor
_____________________________________________

Rename and drop the old table:

DECLARE @drop_error int, @rename_error int, @renameold_error int
-- Start a transaction.
BEGIN TRAN
SELECT @drop_error = 0
if exists (select * from dbo.something where id = object_id(N'[user].[old_products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table old_products
end
SELECT @drop_error = @@ERROR

EXEC sp_rename 'products', 'old_products'
SELECT @renameold_error = @@ERROR

EXEC sp_rename 'temp_products', 'products'
SELECT @rename_error = @@ERROR

-- Test the error values.
IF @drop_error = 0 AND @renameold_error = 0 AND @rename_error = 0
BEGIN
-- Success. Commit the transaction.
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
ROLLBACK TRAN
END
__________________________________________________

Shoud I just add:

GO

ALTER TABLE [user].[products] WITH NOCHECK ADD
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[PRODUCTROWID]
) WITH FILLFACTOR = 95 ON [PRIMARY]
GO

At the end of the last code above??

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 10:40:22

Why don't you just add id int IDENTITY(1,1) to your temp table. And what's with the CURSOR? Why don't you just use a SET based operation?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources