July 23, 2003 at 9:17 am
Something is wrong with the way I'm trying to use the identity function on my SQL 7 server. When I pass a regular number all is well. However, when I pass a variable (below example) the quey starts with an identity seed of 7998704 regardless of what was passed within the value. Help.
declare @val1 int
select @val1 = max(id) +1 from table1
select field1,
IDENTITY(int, @val1,1) AS [ID], -- problem
into #temp_list
from table2
July 23, 2003 at 9:31 am
Testing in SQL2000, it does not even allow a variable when used with the IDENTITY function. You could do something like this, however.
exec ('select field1,
IDENTITY(int,' + @val1 + ',1) AS [ID]
into #temp_list
from table2
select * from #temp_list order by [ID]')
July 25, 2003 at 4:15 am
It is interesting what you said about SQL2000.
Well I couldn't get the previous recomendation to work. And unfortunately my target table requires special formating for its identity field. So directly inserting would be cumbersome if not impossible.
What I've found is that it doesn't appear possible to pass a variable to the IDENTITY function. It also doesn't appear possible to pass a variable to the IDENTITY property.
However, this does work. Create a temp table with the Identity property (script 1 below). Since the seed can't be set, set it to whatever (1) and we'll fix it.
Next, reseed the temp table with the appropriate value from the target table (script 2 below). Now, you're ready to insert the new records into the temp table and once they are complete they can be sent to the final target table.
Regarding the special formating for the ID field on the target table on insert; The field is varchar(15) left padded with zeros. See Script 3 below.
-- *** Script 1
Create table #temp_users (
id_num int IDENTITY(1,1),
FULL_NAME varchar(128), -- field 8
Login_Name varchar(30) -- field 101
)
-- *** Script 2
declare @id_val int
select @id_val = max(Entry_Id) + 1 from ARSystem..user_x
DBCC CHECKIDENT (#temp_users,RESEED,@id_val)
-- *** Script 3
select right(replicate('0',15) + convert(varchar(15),id_num),15),
FULL_NAME,
Login_Name, -- Login Name
from #temp_users
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply