I am redeveloping an application and found something I have not seen before.  It's a customer table where  the first two columns are CustomerID - GUID, required Yes, and CustID, AutoInc, required no.  This is not unusual but here is where it gets a little weird.  The table also has a column called CustomerNO, string, 10 bytes and required no.  There is also an index ByCustomerNO, unique yes where the index field is CustomerNo.  As it turns out the CustID and the CustomerNO are the same in this table.  They don't have to be, but in this table they are.

So when I try to insert, because of the index, I need to have a unique CustomerNO and to follow the convention of the table, make it equal the auto incremented CustID.   

Question - how do I insert a record where the CustomerNO is the same as the auto incremented CustID.  Is it possible to do this within the same insert or is this a two step process?

Why would anyone do something like this?

>>>Question - how do I insert a record where the CustomerNO is the same as the auto incremented CustID.  Is it possible to do this within the same insert or is this a two step process?

I think a two-stepper -  post first, then fetch the auto-inc value, then post again.  That said, maybe use the "AfterInsert" event to obtain the autoinc value, then do a quick edit to post the CustomerNo value.

>>Why would anyone do something like this?

Once you start to refactor, you discover the "why".

John

John Easley wrote:

>>>Question - how do I insert a record where the CustomerNO is the same as the auto incremented CustID.  Is it possible to do this within the same insert or is this a two step process?

I think a two-stepper -  post first, then fetch the auto-inc value, then post again.  That said, maybe use the "AfterInsert" event to obtain the autoinc value, then do a quick edit to post the CustomerNo value.

>>Why would anyone do something like this?

Once you start to refactor, you discover the "why".

John


This is what I ended up coming up with.   

START TRANSACTION;
insert into customers
(LastName, CustomerNo)
values ('Joe', 'CustomerIN');
update Customers
set CustomerNo = cast(ident_current('Customers') as char(10))
where CUSTID = (ident_current('Customers'));
COMMIT;

Very nice!

>>> Owen wrote:

This is what I ended up coming up with.   

START TRANSACTION;
insert into customers
(LastName, CustomerNo)
values ('Joe', 'CustomerIN');
update Customers
set CustomerNo = cast(ident_current('Customers') as char(10))
where CUSTID = (ident_current('Customers'));
COMMIT;