automatic insert of a counter value

Question

Post reply

automatic insert of a counter value

petercabus

SSC Veteran

Points: 241
More actions
August 13, 2003 at 3:16 am

#159574

Hi all,
I have the following problem.
I have a table (SoldItemsImport) with one field:
SoldItem (varchar(100))
I have another table (SoldItems) that contains the following fields:
SoldItemID (integer)
SoldItem (varchar(100))
The SoldItemID is actually a counter but not an Identity value, so it is not incremented automatically.
Is it possible to create an insert statement to add the items from SoldItemsImport into SoldItems, without using a cursor or a temp table?
My current method is to use a temp table with an Identity value. It works, but I'd rather use a straight insert statement.
Thanks,
Peter

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

brokenrulz SSCrazy Points: 2261 More actions · Answer 1

Try using a trigger on the solditemsImport table.

Relationships are like Banks.You Invest the Principal and then get the Interest.

He who knows others is learned but the wise one is one who knows himself.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

Try this:

insert SoldItems

select SoldItem

from SoldItemsImport

where not exists (select SoldItem from SoldItemsImport where SoldItemsImport.SoldItem = SoldItems.SoldItem)

Jeremy

petercabus SSC Veteran Points: 241 More actions · Answer 3

Brokenrulz,

I cannot change table "soldItems". This table is generated by a third party application.

Jeremy,

The problem is not selecting the items from SoldItemsImport. I need all the rows from SoldItemsImport in SoldItems. The problem is the SoldItemID. It needs an incremental value starting from Max(SoldItems).

The tables/values are just examples (simplified) to explain the problem.

I need something like the Identity-function, but that only works with a "select into" not an "insert into".

Thanks for the feedback.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 4

OK - another stab.

declare @continue char(1)

set @continue = 'Y'

set @rowcount 1

while @continue = 'Y' begin

insert SoldItems

select SoldItem, max(SoldItemId) + 1

from SoldItemsImport, SoldItems

where not exists (select SoldItem from SoldItemsImport where SoldItemsImport.SoldItem = SoldItems.SoldItem)

if @@rowcount = 0 set @continue = 'N'

end

Basically, this will loop and insert one row at a time and insert the value with with an ID of 1 greater than the current max value.

I haven't tested the sql but I'm sure you get the picture.

Jeremy

petercabus SSC Veteran Points: 241 More actions · Answer 5

Jeremy. Your solution is very similar to using a cursor.

My solution was:

==============================

declare @MaxValue as int

Select @maxValue = Max(*) from SoldItems

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO ##SoldItemsTemp

FROM SoldItemsImport

Insert into SoldItems

select ID_Num + @maxValue from ##SoldItmesTemp

============================

Which runs faster than either a cursor or nested insert/selects like your solution.

I was really looking for a function that automatically generated incremental numbers to use in the insert statement.

Peter

Graeme100 SSCertifiable Points: 7925 More actions · Answer 6

Hi,

Why don't you use a stored procedure with an OUTPUT parameter.

Once you have updated SoldItems you can pass a counter into a procedure that finds the MAX id and adds one to it then returns the answer. Sort of like

exec Proc_FindNextNum '1',@NewID OUT

The procedure Proc_FindNextNum would simply find the max ID and add the '1' to it.

Any good?

petercabus SSC Veteran Points: 241 More actions · Answer 7

Probably the best solution. But I'll use a function instead of a stored procedure.

Thanks,

Peter