Using Identity in a stored proccedure

Transact-SQL
1

Good after noon, i am most likely certain that this is an easy task but for some reason am just not able to grasp it.

In a stared proceedure i have the following:

declare @tblTemp table(consumerid int Identity(1,1), consumername varchar(50), consumeracct varchar(5), tags varchar(10))

Begin

Insert into @tblTemp (comsumername, consumeracct, tags)
select consumername, consumeracct, tags from dp_data

select * from @tblTemp

end

this is just the begining to what i am trying to accomplish but where i am having trouble is I want to be able to assign a variable to the Identity for the starting position. But no matter what i try @ $ % it will not let me assign a variable to the identity.

So then i found something about using @@identity and it was said that you could see what the last number used was, thought maybe if you made it = to some value it would do the same but alas it does not.

So can someone assist me and tell me how i can make the starting point of the Identity a variable?

THanks and have a great day.

2

Instead of declaring the identity to start at 1, declare it to start at whatever value you want to start it at. For example, if you want it to start at 4000,

declare @tblTemp table(consumerid int Identity(4000,1), consumername varchar(50), consumeracct varchar(5), tags varchar(10))

3

That does not answer the question i am using a select max(consumerid) to get the most recent from a table then if i have to add to the table this is where i want to start the count at. Hence the reason i would like to be able to make the starting point a variable.

4

For a table variable, you cannot reseed the identity value except at CREATE time. You alternatives would be

a) Use a temp table or a user table and use DBCC RESEED. Example:

CREATE TABLE #tblTemp(consumerid int Identity(1,1), consumername varchar(50), consumeracct varchar(5), tags varchar(10))

DBCC CHECKIDENT(#tblTemp, RESEED, 4000);

b) Don't use an identity column. Instead create and use a SEQUENCE

By the way, in SQL 2012 and later, identity columns are not guaranteed to always increment by 1 (unless you set TRACE FLAG 272

5

ok let try to do a better job of explaining what i was tasked to do and let all know that im still fairly green when it comes to sql. everything i have learned was reading, google, youtube and trial and error.

So the company i work for acquired a used access control system. it creates its own tables, views and stored proceedures. i was able get some info like what table contains the employee information.

now i was also given the payroll database table.

i want to create a stored proceedure that will run and in the event of any changes will update the access control from the payroll data base.

the problem is in the access control the one column is not set to auto increment so i cant just insert updated changes and im not changing their tables without knowing more about their layout.

Now in the event of changes, my query would pick out the changes, then starting at the next number would auto number the entries and insert them into the access control table.

the part i am stuck at is the numbering. that is why i asked if i can use a variable for identity.

but then you say in 2012 and later identity does not work well without other proceedured done. of course dont think i will need to worry about that being we are only on 2005 and i dont think they are going to pay to upgrade.

Most of the stored proceedure i have done im just having issues with this incrementing number

6

This sounds like an exceptionally bad idea.

The license for any Access Control System I have come across forbids the direct update of any of the tables.
There is usually some sort of data import table for updating details which for people coming from a payroll system will usually be keyed on the resource number.
(ie There should be no need to increment anything.)

If you are unsure of what exactly is in the Access Control System (usually because security personnel can alter the data you have entered), then generate a shadow table of the relevant contents which can be compared against the payroll extract. Only the differences need be entered into the data import table.