Add consecutive id in Inset mode.

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)

Add consecutive id in Inset mode.

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-02 : 06:18:16

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
Yossi

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-12-02 : 11:42:49

Is it possible to create the ID column as identity(1,1)? This will auto increment it for you. Thats the easiest answer..... Will that work for you?

-----------------------
SQL isn't just a hobby, It's an addiction

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-03 : 03:50:48

quote:

Is it possible to create the ID column as identity(1,1)? This will auto increment it for you. Thats the easiest answer..... Will that work for you?

-----------------------
SQL isn't just a hobby, It's an addiction

Sorry, No.
The Id column has a meaning.
If its current value is 20 for example,each insert of new row should get the next value.
Identity won't work here.
Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-03 : 04:03:14

I'm confused. Do you mean

Each new row gets the *same* id, as in

Current id value = 15
10 rows to add

All 10 rows get id value = 15

Or

Each new row gets a different id, as in

Current id value = 15
10 rows to add

Rows get IDs

16,17,18,19,20,21,22,23,24,25

If it's the second of those, then I don't see why you can't use identity.

If the first, then maybe you could have a separate table that stores the current ID value, then insert this into your new records and update the ID value in the table when you are done. You could probably do that as a transaction depending on what kind of things you've got going on at the same time and the data volumes.

-------
Moo.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-03 : 07:01:11

quote:

I'm confused. Do you mean

Each new row gets the *same* id, as in

Current id value = 15
10 rows to add

All 10 rows get id value = 15

Or

Each new row gets a different id, as in

Current id value = 15
10 rows to add

Rows get IDs

16,17,18,19,20,21,22,23,24,25

If it's the second of those, then I don't see why you can't use identity.

If the first, then maybe you could have a separate table that stores the current ID value, then insert this into your new records and update the ID value in the table when you are done. You could probably do that as a transaction depending on what kind of things you've got going on at the same time and the data volumes.

-------
Moo.

Hi,
Sorry if you got confused.
Its the second of your choices.
I need to insert a certain amount of rows and make sure that the first of this rows will have Id value that I know and from that row ferther values in Id field will be increased.
using Identity by itself will increas values each time i will insert rows. Its good but there are times the starting value is 0.
Hope I'm clearer now.
Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-03 : 07:13:38

Possibly - and triggers are not my strong point - you could use a table that holds the value you want to start from, then have a trigger on the table you update that adds one to this value every time a record is inserted.

-------
Moo.

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-12-03 : 07:46:28

quote:

Possibly - and triggers are not my strong point - you could use a table that holds the value you want to start from, then have a trigger on the table you update that adds one to this value every time a record is inserted.

-------
Moo.

Great,
I will try that.
Thanks a bunch mate.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-12-03 : 11:58:17

Take a look at instead of insert triggers. Although I'd watch the number of inserts your making on this table if your using triggers like this. A trigger on a table you insert into alot may severely slow preformance.

-----------------------
SQL isn't just a hobby, It's an addiction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources