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

Transact-SQL (2000)

SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-06 : 21:36:09

craig writes "WINDOWS XP - SQL SERVER 2000 - SP2

Hi,
I am trying to populate a table using the following syntax. SELECT
IDENTITY(INT,1,1),
Col1,
Col2
INTO Temp_TableName
FROM
SomeOtherTable

After some manipulation, this will then populate the master data. But I need to dynamically set the seed of the IDENTITY function. I have tried the following

** Declare and obtain max value from Master table key and add 1

IDENTITY(INT,@NextMasterDataKey,1)

I have also tried to put a select statement in place of the variable. The only other way I can think of doing this, is to create the whole statement as a string and execute the string.... I really could do with avoiding this as the statement is huge and would be a nightmare to debug.

Is there any way of dynamically setting the IDENTITY seed in sql?

Thanks & Regards

Craig"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-07 : 01:52:38

The only way is to use Dynamic SQL. But the scope of Temp_TableName will be lost after you execute it.

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-07 : 02:17:17

use Dynamic SQL with global temp table

declare @sql varchar(1000)

select @sql = 'SELECT IDENTITY(INT,' + convert(varchar(10), @NextMasterDataKey) + ',1) as id, 
Col1, Col2
INTO ##Temp_TableName
FROM SomeOtherTable'
exec (@sql)
select * from ##Temp_TableName

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-07 : 03:12:56

You dont need to use convert it to varchar

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-07 : 03:30:38

Actually, you have to. I never relied on implicit conversion as i cannot remember which can convert and which can't.
Anyway without the explicit convert, this is the error "Syntax error converting the varchar value 'SELECT IDENTITY(INT,' to a column of data type int."

According to BOL

quote:
Data Type Precedence
When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted to the other. The data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
This is the precedence order for the Microsoft® SQL Server™ 2000 data types:

sql_variant (highest)
datetime
smalldatetime
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar
nchar
varchar
char
varbinary
binary (lowest)

The precedence order for int is higher than varchar.

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-07 : 06:15:29

Well. If you dont use @sql, then no need of conversion



exec ('SELECT IDENTITY(INT,' + @NextMasterDataKey + ',1) as id, 
Col1, Col2
INTO ##Temp_TableName
FROM SomeOtherTable')

select * from ##Temp_TableName

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources