IDENTITY: Defining the 'seed' value.

[eao]

I'd like to use a value from another table to set the seed value for a field in a table variable.

This on its own parses ok:

--Variable to set the MU.Sequence field.
DECLARE @Seq int
SET @Seq = (SELECT (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_')

And this code...

--Variable to set the MU.Sequence field.
DECLARE @Seq int
SET @Seq = (SELECT (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_')

--Table variable for MU data.
declare @tmpMU table (
	NewSeq int IDENTITY(@Seq,1), 
	CompanyID nvarchar (30)
	CompanyName nvarchar (50)
	)

...returns this error:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@Seq'.

Is this not allowed? What are my options? Thanks!

 

[genomon]

Will this work? No time to try it here right now...

DECLARE @Seq int

SELECT @Seq = (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_'

"No matter what happens, somebody will find a way to take it too seriously."

 

[ESquared]

I can't figure out what you're trying to do. When you use the identity() function, you are declaring that a column will be an automatically numbered one... an event that happens once and then uniquely identifies things. So what need could you have for a temp table that starts with a value from another table? Please explain what you are trying to accomplish.

For one thing, you don't have to change the seed value, you can just add @Seq - 1 any time you want to get the correct value, or start with identity(0, 1) and add @Seq.

Please give us the whole picture because often we can give useful advice about that larger picture which may not have been immediately apparent to you.

 

[eao]

I want my autonumbered column to start at a particular value because I am importing records into a table that has values in it already.

Basically, I have this table, smsysrecnum, that stored the last used sequence number for all user tables in the database.

Here's the entire query:

USE SDE
GO
--Variable to set the MU.Sequence field.
DECLARE @Seq int
SET @Seq = (SELECT (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_')

--Table variable for MU data.
declare @tmpMU table (
	NewSeq int IDENTITY(@Seq,1), 
	CompanyID nvarchar (30)
	CompanyName nvarchar (50)
	)

--Populate the temporary table with the data to be imported.
INSERT INTO @tmpMU (CompanyID, CompanyName)
	SELECT Replace (Replace(Replace(Replace(Replace(Replace(m.name, 'a', ''), 'e', ''), 'i', ''), 'o', ''), 'u', ''), ' ', '') AS CompanyID, m.name as CompanyName
		FROM MU as m
			LEFT OUTER JOIN SDE._SMDBA_._COMPANY_ as c
				ON m.Name = c.CODE
		WHERE c.CODE is null

--Run the import.
INSERT INTO SDE._SMDBA_._COMPANY_ (Sequence, CODE, NAME)
	SELECT NewSeq, CompanyID, CompanyName
	FROM @tmpMU

--Get the last used sequence number.
SET @Seq = (SELECT max(sequence) FROM SDE._SMDBA_._COMPANY_)

--Update the record number table with the last used Seq value.
UPDATE SDE.dbo.smsysrecnum
	SET RecNum = @Seq
	WHERE Name = '_COMPANY_'

 

[eao]

genomon,

SELECT @Seq = (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_'

...returns the same error.

 

[fxthoth]

I can't help with table as variable, but this may work with a temp table:

DECLARE @Seq int;
SET @Seq = 8;

CREATE TABLE #tmpMU(
    CompanyID nvarchar (30),
    CompanyName nvarchar (50)
    )

EXEC('ALTER TABLE #tmpMU ADD NewSeq INT PRIMARY KEY IDENTITY('+@Seq+',1)')

INSERT INTO #tmpMU (CompanyID,CompanyName)
	VALUES (N'FOO',N'BAR')

SELECT * FROM #tmpMU

I know it is not good idea to use exec, but it is the last what work for me...

 

[vongrunt]

Can you simply leave identity at (1, 1), then do this:

--Run the import.
INSERT INTO SDE._SMDBA_._COMPANY_ (Sequence, CODE, NAME)
    SELECT NewSeq [!]+ @Seq - 1[/!], CompanyID, CompanyName
    FROM @tmpMU

?

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[ESquared]

vongrunt, that's what I said, too! Don't try to make the identity seed in the temp table right, just add. Make it identity(0, 1) and you don't even have to add the -1.

eao, there's another problem I see which is that you are not managing concurrency properly: two people try to do this same action within a very short time, they are going to collide with each other.

Client A:
[ul][tt]SET @Seq = (SELECT (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_')
@Seq for client A is 25.[/tt][/ul]

But Client B runs at almost the same moment and gets this statement in before A processes its next statement:
[ul][tt]SET @Seq = (SELECT (RECNUM + 1) FROM SDE.dbo.SMSYSRECNUM WHERE Name = '_COMPANY_')[/tt][/ul]

Then client A executes its next statement:
[ul][tt]INSERT INTO SDE._SMDBA_._COMPANY_ (Sequence, CODE, NAME)
SELECT NewSeq, CompanyID, CompanyName
FROM @tmpMU[/tt][/ul]

So because of @Seq, Newseq here is a range starting with 25.

Then client B executes its next statement:
[ul][tt]INSERT INTO SDE._SMDBA_._COMPANY_ (Sequence, CODE, NAME)
SELECT NewSeq, CompanyID, CompanyName
FROM @tmpMU[/tt][/ul]

EXPLOSION! Collision: it's trying to use the same range starting with 25 as Client A.

You always have to think about concurrency in databases, and about making work atomic when concurrency is a factor. In this case, you don't even have to start using table locks and transactions because you can do your retrieval and update of the smsysrecnum table in a single statement (and a single statement is always atomic, consistent, isolated, and durable). (Unless your insert fails and you consider it a waste to have used up some of the recnums needlessly, then you need to BEGIN TRAN and ROLLBACK TRAN upon an error condition.

Try this:

DECLARE @RecNum int

--Table variable for MU data.
DECLARE @tmpMU table (
    NewSeq int identity(0, 1), 
    CompanyID nvarchar (30),
    CompanyName nvarchar (50)
   )

--Populate the temporary table with the data to be imported.
INSERT INTO @tmpMU (CompanyID, CompanyName)
    SELECT Replace (Replace(Replace(Replace(Replace(Replace(m.name, 'a', ''), 'e', ''), 'i', ''), 'o', ''), 'u', ''), ' ', '') AS CompanyID, m.name as CompanyName
        FROM MU as m
            LEFT OUTER JOIN SDE._SMDBA_._COMPANY_ as c
                ON m.Name = c.CODE
        WHERE c.CODE is null

UPDATE SDE.dbo.smsysrecnum
    SET @RecNum = RecNum + 1, RecNum = @RecNum + (SELECT Count(*) FROM @tmpMU) - 1
    WHERE Name = '_COMPANY_'

--Run the import.
INSERT INTO SDE._SMDBA_._COMPANY_ (Sequence, CODE, NAME)
    SELECT NewSeq + @RecNum, CompanyID, CompanyName
    FROM @tmpMU

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]