Wednesday, March 7, 2012

Identity column question

Trying to create a custom dts package that imports data from excel spreadsheet to db. One of the issues is that the columns in the main entity tables are not identity columns. So how do you get the appropriate PK?

Well I thought the following would work but it doesn't. Any ideas?

Basically trying to use the variable as the starting point in the temp identity declaration. Is this a syntax error or a "no-no".

declare @.maxid int
select @.maxid = (select max(objectid)+1 from object)

create table #service (
ObjectId int identity (@.maxid,1),
ModifiedAt datetime,
ModifiedBy int,
ObjectTypeId int,
[Name] nvarchar(100),
ServiceType nvarchar(100),
CategoryId int,
IsVisible int)

insert into #service (ModifiedAt,ModifiedBy,ObjectTypeId,[Name],ServiceType,IsVisible)
select getdate(),1,1,service,ServiceType,1 from importcreate table #service (
ObjectId int identity (1,1),
ModifiedAt datetime,
ModifiedBy int,
ObjectTypeId int,
[Name] nvarchar(100),
ServiceType nvarchar(100),
CategoryId int,
IsVisible int)

DBCC CHECKIDENT (#service, RESEED, @.maxid)|||thank-you very much

No comments:

Post a Comment