October 1, 2014 at 11:41 am
Hello,
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
Thanks in advance for all the help
October 1, 2014 at 11:55 am
Is the numeric sequence for Accounts and Contacts shared, or are they separate counters?
For example, which set below is more appropriate:
Set 1 - Separate counters
A001234
C005555
C005556
A001235
Set 2 - Shared counter
A001234
C001235
C001236
A001237
October 1, 2014 at 11:57 am
They are separate counters
October 1, 2014 at 12:00 pm
My advice would be to use an identity column along with a char column and create a computed column with both. It will result in easier maintenance. If the C is constant, you can leave it as formatting in the front-end without storing it.
Here's an example replacing the identity with a normal column.
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n
FROM E a, E b
),
cteTally AS(
SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) n
FROM E2 a, E2 b
)
SELECT STUFF( n + 1000000, 1, 1, 'C')
FROM cteTally
You could use sequences as well.
October 1, 2014 at 12:04 pm
Do you need it as a function? Part of a stored procedure?
Luis is spot on with the advice of maintaining separate columns for the Leter and the Counter. If you don't have the ability to make that call, maybe this will help as a start:
;with Identifiers as
(
SELECT 'C004658' as contact_id
union select 'C004659'
union select 'A001234'
union select 'A001255'
)
select
contact_id as 'Highest contact_id',
left(contact_id,1) + RIGHT('00000' + CAST(CAST(substring(contact_id,2,6) as int) + 1 as varchar), 6) as 'Next contact_id'
from Identifiers
where
CAST(substring(contact_id,2,6) as int) =
(select MAX(CAST(substring(contact_id,2,6) as int)) from Identifiers where left(contact_id,1)='A' --or 'C')
October 1, 2014 at 12:08 pm
Why not simply maintain 2 separate tables and union them when needed? What value do you get out of jamming two separate concepts into the same structure? For that matter - why not simply keep the distinction between acocunt and contact separate from the incremented column? What happens when I need to delete account 1234 and I already have 25000 accounts in (i.e. the sequence will now be "broken").
If contacts and accounts are that similar in nature - what happens when a contact needs to "become" a contact? is there any case when they switch back and forth?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 1, 2014 at 12:18 pm
I need it as part of a stored procedure that will run every 5 minutes to assign these ids to new contacts that are being added,
thanks,
Petr
October 1, 2014 at 12:22 pm
This is part of an integration design and I do not control the input (the source DB is on the linked server that is not ours).
I basically need this to loop through the contacts that do not have a contact_id and assign it to them as they were added to the table.
thanks,
Petr
October 1, 2014 at 12:43 pm
vecerda (10/1/2014)
Hello,I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
Thanks in advance for all the help
My advice in a nutshell is don't do this. There are three major reasons this is a horrible design choice. First, you are now combining two pieces of information in a single column (RowType and RowID). This is in direct violation of 1NF.
Second, unless you use a sequence you are going to have major challenges with concurrency.
Thirst, this is a serious PITA to deal with.
If you are stuck with it then I would deal with this as a computed column like Luis already suggested. That would also eliminate the need for a procedure to run every 5 minutes. You don't need the procedure at all. Something along these lines should work.
create table Something
(
SomeID int identity not null,
AccountType char(1),
MyIncrementingAlphaColumn as AccountType + RIGHT(REPLICATE('0', 6) + CAST(SomeID as varchar(6)), 6)
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 1, 2014 at 2:25 pm
--Probably easiest would be to put the control values into a separate table, and if necessary use exclusive locks on that table when seeding that table.
CREATE TABLE dbo.control_table (
prefix char(1) NOT NULL,
suffix char(6) NOT NULL,
control_value AS CAST(prefix + suffix AS char(7)),
CONSTRAINT control_table__PK PRIMARY KEY ( prefix )
)
------------------------------------------------------------------------------------------
SET NOCOUNT ON
--after preparing the incrementing code,
--do an initial load of the current highest values into the control_table
/*
INSERT INTO dbo.control_table
SELECT 'A', SUBSTRING(alphanumeric, 2, 6)
FROM dbo.your_data_table --WITH (TABLOCKX) --if needed to insure you get the highest value
WHERE alphanumeric LIKE 'A%'
INSERT INTO dbo.control_table
SELECT 'C', SUBSTRING(alphanumeric, 2, 6)
FROM dbo.your_data_table --WITH (TABLOCKX) --if needed to insure you get the highest value
WHERE alphanumeric LIKE 'C%'
*/
INSERT INTO dbo.control_table VALUES('C', '004658')
------------------------------------------------------------------------------------------
--code to increment a given prefix
DECLARE @prefix_to_increment char(1) --set to 'A' or 'C'
SET @prefix_to_increment = 'C'
DECLARE @new_value TABLE (
prefix char(1) NOT NULL,
suffix char(6) NOT NULL
)
UPDATE dbo.control_table WITH (TABLOCKX) --insure only one value assigned at a time
SET suffix = RIGHT('000000' + CAST(suffix + 1 AS varchar(6)), 6)
OUTPUT
INSERTED.prefix, INSERTED.suffix INTO @new_value
WHERE
prefix = @prefix_to_increment
SELECT TOP (1) prefix + suffix AS new_value
FROM @new_value
DROP TABLE dbo.control_table --for testing only, of course
Edit: Added code tags.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 1, 2014 at 2:46 pm
I'm sure that using sequences would be easier if 2 separate counters are needed. Sequences can handle multiple row inserts and should be easier to maintain.
October 1, 2014 at 3:12 pm
True, if they actually are using SQL 2012. Sometimes you can't be sure :-D.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply