Sequential Key Issues 1

[mcconnellj]

My source table has a integer key that sequentially numbers all the records inserted. I copied the records from that table into another with a slightly different structure for further staging. I did not set any constraints/properties on the second table's corresponding integer key, because I wanted the same values so matching between the tables would be simple.

I am now at a step that I want to insert more records to the second table. However, I want the new integer keys to start from the max value and increment by 1 for each record appended.

I tried an alter table statement with the indentity property, but I had no success.

Any other ideas - Thank You!

 

[EdwinGene]

You could try an Insert trigger which selects the max() id field value and increments it by one, then inserts that value into the id column.

 

[mcconnellj]

Thanks EdwinGene. Any ideas when doing a bulk insert?

 

[donutman]

Did you set the seed property correctly? I usually do it from EM and don't have a problem. What error did you get doing the Alter Table?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mcconnellj]

donutman,

Here is the alter statement. It works with a create table statment:

ALTER TABLE CDW2POLICY_TRANSACTION
ALTER COLUMN POLICY_TRANSACTION_KEY INT IDENTITY(2766188, 1)

Error:
Incorrect syntax near the keyword 'IDENTITY'.

Also, my preference would be to have the seed value pulled from a parameter. I do not know if that will work because I haven't gotten pass the alter table statement.

Thanks!

 

[donutman]

I'm not sure but try this:
ALTER TABLE CDW2POLICY_TRANSACTION
ALTER COLUMN POLICY_TRANSACTION_KEY INT IDENTITY(2766188, 1) PRIMARY KEY
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mcconnellj]

Thanks - no luck

 

[donutman]

OK, I'm guessing again, but I don't think you can alter a table to change a column to an identity column. EM creates a new table and copies the old data, then drops the original and renames the new table, that way it gets away with the change.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sunila7]

To change and existing column to an Identity field you cannot use ALTER column command. If you use EM to do this it actually creates a temporary table with Identity column and copies data from the old table to new table and then drops the old table and then renames the new table back to old table.
This is a sample script that gets created by EM

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
	(
	id int NOT NULL IDENTITY (1, 1),
	xyz char(10) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
	 EXEC('INSERT INTO dbo.Tmp_Table1 (id, xyz)
		SELECT id, xyz FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT

Sunil