February 19, 2009 at 2:22 pm
can you give examples of :
3. Get the last assigned customer prefix
4. Insert (as a set) the new customers, along with the correct customer prefix based on #2, #3 and a row_number() offset.
5. If you still need to, update the "entity sequence" with the number of new customers plus #2.
February 19, 2009 at 2:52 pm
paul.starr (2/19/2009)
can you give examples of :3. Get the last assigned customer prefix
4. Insert (as a set) the new customers, along with the correct customer prefix based on #2, #3 and a row_number() offset.
5. If you still need to, update the "entity sequence" with the number of new customers plus #2.
[font="Verdana"]
I can give you some code as an example, sure.
if object_id('dbo.Customer') is not null
drop table dbo.Customer;
create table dbo.Customer(
CustomerID int identity not null primary key,
CustomerPrefix varchar(3) not null unique,
CustomerName varchar(100) not null unique
);
insert into dbo.Customer(
CustomerPrefix,
CustomerName
)
select 'AAA', 'A as in Aardvark' union all
select 'AAB', 'AB, CD goldfish';
go
if object_id('dbo.EntitySequence') is not null
drop table dbo.EntitySequence;
create table dbo.EntitySequence(
EntitySequenceID int identity not null primary key,
EntityName nvarchar(128) not null unique,
CurrentSequence int not null
);
insert into dbo.EntitySequence(
EntityName,
CurrentSequence
)
select 'dbo.Customer', 2;
go
--
-- creating a table variable here just to hold some examples of
-- new customers. You can replace this with whatever code you
-- actually use to get your new customers
--
declare @newCustomerExample table(
CustomerName varchar(100) not null primary key
);
insert into @newCustomerExample(
CustomerName
)
select 'MNO Golfish' union all
select 'CDBD Eyes';
declare @newCustomerRows int,
@lastCustomerPrefix varchar(3),
@customerPrefixBase int,
@aValue int,
@letters int;
set @aValue = ascii('A');
set @letters = ascii('Z') - @aValue;
--
-- create a transaction to ensure nothing else comes along
-- and makes changes to customers while we are doing this
begin transaction BulkCustomers
--
-- look up the last assigned customer prefix
--
select top 1
@lastCustomerPrefix = CustomerPrefix
from dbo.Customer with (tablock holdlock)
order by
CustomerID desc;
--
-- calculate a numeric version of the customer prefix, so we
-- can calculate the correct value during the insert simply
-- by adding two numbers together, then converting the result
-- back to a string
--
set @customerPrefixBase =
(ascii(substring(@lastCustomerPrefix, 1, 1)) - @aValue) * @letters * @letters +
(ascii(substring(@lastCustomerPrefix, 2, 1)) - @aValue) * @letters +
(ascii(substring(@lastCustomerPrefix, 3, 1)) - @aValue);
--
-- now create the new customers along with the prefix
--
with
NewCustomer as (
select CustomerName,
@customerPrefixBase + (row_number() over (order by CustomerName)) as CustomerPrefixBase
from @newCustomerExample
)
insert into dbo.Customer(
CustomerName,
CustomerPrefix
)
select CustomerName,
char(@aValue + (CustomerPrefixBase / @letters / @letters)) +
char(@aValue + (CustomerPrefixBase / @letters) % @letters) +
char(@aValue + (CustomerPrefixBase % @letters))
from NewCustomer;
set @newCustomerRows = @@rowcount;
--
-- now move the entity sequence number
--
update dbo.EntitySequence
set CurrentSequence = CurrentSequence + @newCustomerRows
where EntityName = 'dbo.Customer';
--
-- remember to commit to release the locks
--
commit transaction;
--
-- let's see the results
--
select *
from dbo.Customer;
select *
from dbo.EntitySequence;
[/font]
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply