Need to increment column in 3rd party db

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

General SQL Server Forums

New to SQL Server Programming

Need to increment column in 3rd party db

Author

Topic

sync00
Starting Member

24 Posts

Posted - 2009-05-04 : 15:05:38

I have a 3rd party db where I need to insert records into one of the tables. The id column needs to increment and it is not defined as an identity column.

I'm looking for suggestions on how to handle this.

I'm tried to do this with a temporary table which does have an identity column. I created the following code.


DECLARE @MyCounter int;

SELECT @MyCounter=MAX(Trans_ID)
FROM AR_Transactions

CREATE TABLE dbo.AR_Trans_test ( Trans_ID smallint identity(@MyCounter,1), TestName char(20) )

The create statement doesn't work with the variable.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-04 : 16:14:14

[code]
DECLARE @MyCounter int;
DECLARE @SQL varchar(1000)

SELECT @MyCounter=MAX(Trans_ID) FROM AR_Transactions

SET @MyCounter=@MyCounter+1 -- maybe or not if max(Trans_Id) is already used or not...

SELECT @SQL=
'CREATE TABLE dbo.AR_Trans_test ( Trans_ID smallint identity('+convert(varchar(10),@MyCounter)+',1), TestName char(20) )'

EXEC(@SQL)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

sync00
Starting Member

24 Posts

Posted - 2009-05-04 : 16:35:24

Excellent.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources