Identity Issue

[BrasilianGuy]

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??

 

[DrSql]

What error you are getting. Why dont you execute this script in QA and post your error. May be we can provide more details.

Dr.Sql
Good Luck.