TSQL - Making a counter table

Hi Experts,

I have a column with a Certificate #

TableName.Cntr3

I would like to have the # sequential
I guess I have to make a separate table for numbering?

I need help with the setup on this?

Please help and thanks...
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Explain your requirements with more details, as this smells like either an Identity column, or an insert trigger that gets the max() of a specifica column, then adds one more.
Vitor MontalvãoMSSQL Senior EngineerCommented:
If the Certificate is numeric then as Jim's commented, the field should be an identity column.
But if it's a string then you need to provide us the correct format and some sample data so we can help you better.
Amour22015Author Commented:
Yes this is a identity column that is needed?

So in the table I would need? something to identify a unique number and then the # sequential?

Please help and thanks
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jim HornMicrosoft SQL Server Data DudeCommented:
>Yes this is a identity column that is needed?
Are these questions or statements?

>Explain your requirements with more details
Still waiting..
Amour22015Author Commented:
Hopefully this will give the details needed?

I have a vertical table.
tblAffiliation

Where tblAffiliation.Affiliations = '3A'

The Column:
Cntr3 (int, null)
Number starts with 1 - 9999  is current on what is in the tblAffiliation.Cntr3 Column

Maybe put this in a trigger?
with an external table to keep track of the sequential #?

Please help and thanks
Jim HornMicrosoft SQL Server Data DudeCommented:
A question ends in a question mark ?, which means that you'd like an answer.
A statement ends in a period., which means you are proving us info, and no direct response is expected.

This is a valid question:        What day is today?
This is not a valid question, as it's really a statement with an incorrect use of a question mark:     Today is Sunday?

With this in mind, please re-state your question.
Amour22015Author Commented:
Ok,
Hopefully this will give the details needed? <= this is a question

 I have a vertical table.                                    <= This is a statement
 tblAffiliation

 Where tblAffiliation.Affiliations = '3A'.         <= This is a statement

 The Column:
 Cntr3 (int, null)
 Number starts with 1 - 9999  is current on what is in the tblAffiliation.Cntr3 Column.  <= This is a statement

 Maybe put this in a trigger?                                           <= This is a question  (Is this the best way to handle all new record?)
 with an external table to keep track of the sequential #'s?  <= This is a question (Is this the best way to handle all new record?)

If yes please help with all details.   <= This is a statement

 Please help and thanks.  <= This is a statement
Vitor MontalvãoMSSQL Senior EngineerCommented:
Amour, you're keeping giving us the same information. Can you add something more?

For example, why are you filtering the data (Where tblAffiliation.Affiliations = '3A'.)?
Should we count on Certificate Numbers to be different by Affiliation? Or Certificate Number will be sequential and independent from the Affiliation? If is this last case then just set the Cntr3 field to be identity and every time you insert a new record a new sequential number will be set automatically to Cntr3 column.
Amour22015Author Commented:
Q.) For example, why are you filtering the data (Where tblAffiliation.Affiliations = '3A'.)?
 Should we count on Certificate Numbers to be different by Affiliation? Or Certificate Number will be sequential and independent from the Affiliation? If is this last case then just set the Cntr3 field to be identity and every time you insert a new record a new sequential number will be set automatically to Cntr3 column.

A.) No, Cntr3 column is not sequential to All Folders (3A = 1 Folder) it is only sequential to one Folder 3A .

this table has multiple folders and the numbering is separate.

So I will need a separate table to keep track of the sequential number based on 3A.

So where Affiliations = '3A' then do the sequential numbering on Cntr3 Column.

Please help and thanks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
So where Affiliations = '3A' then do the sequential numbering on Cntr3 Column.
Ok, this give us a clear view of what is your use. Then the Identity column is out of question and you need to go for the 2nd option that Jim already gave: "or an insert trigger that gets the max() of a specific column, then adds one more."

So in the insert trigger for that table you need to check if folder is '3A' and if it is you'll need to get the last counter and add one more:
CREATE TRIGGER trgInsAffiliation
ON tblAffiliation AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FOR inserted WHERE Affiliations = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')
END

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
I have set up an external table to keep track of the sequential #'s.

tbl3ACertificateID

That has a matching number to the tblNAsAffiliation table.

So in the new table I have:
[AffiliationID]
      ,[CertID]

In table: tblNAsAffiliation I have the matching number column: AffiliationID and of course the: Cntr3

So your example would need changing?

Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID insert the new value
from tbl3ACertificateID.CertID into tblNAsAffiliation.Cntr3

So I would need a trigger for this?
Also it was mentioned in another post if I am using SQL Server 2012 I can do:
CREATE SEQUENCE MySequence
    START WITH 1
    INCREMENT BY 1 ;
GO

'-- This will return the next sequence value
SELECT NEXT VALUE FOR MySequence

'-- This will return all sequences and their current values
SELECT name, current_value FROM sys.sequences

Open in new window


If so then I would need help setting up the logic behind that also?

Please help and thanks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
All solutions we gave to you works fine in a sequential way but since you want to fill gaps it won't fit all your needs.
How do you want to use your new table to work with those gaps?
Amour22015Author Commented:
I was just asking about the gaps, so we do not need to fill the gaps.

So then which is the best way to go forward?

is it by setting up a trigger?

Or

Using the new technologies with 2012?
This part:
'-- This will return the next sequence value
SELECT NEXT VALUE FOR MySequence

Does that look at the maximum number and give you the next sequence value?

Thanks for helping
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would go for the sequence. They added that feature for some reason ;)
If you check the sys.sequences table it's something similar as your solution to create a new table to keep the counter. No need to reinvent the wheel.
Amour22015Author Commented:
I just found out that my client is on 2012 but the DB's are NOT on 2012 so it looks like I am back on reinventing the wheel.

So I am using an external table:
tbl3ACertificateID

with columns:
[AffiliationID] -- This is the column that can match/link back to tblNAsAffiliation.[AffiliationID] table
       ,[CertID] -- This is the seq. number

I have the main table that holds all the information:
tblNAsAffiliation

So the trigger needs to deal with both tables?

Also I need to mention that tblNAsAffiliation table is a global table meaning that other clients use that table.

So in addition to:
Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID

I also need to check:
WHERE tblNAsAffiliation .[Affiliation] = '3A'

Also maybe I am confused but when the external table:
tbl3ACertificateID
is updated
I have to also update:
tblNAsAffiliation

So all this has to be in the trigger?

Please help and thanks
Amour22015Author Commented:
Ok so I will need help with trying to alter this trigger:
CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON tbl3ACertificateID
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    AffiliationID (PK, int, not null),
    CertID int,
    PRIMARY KEY(AffiliationID, CertID)
    )

 UPDATE a
 SET Cntr3 = ac.Cntr3 + i.row_num
 OUTPUT Affiliations, Cntr3 INTO @updates
 FROM (
     SELECT Affiliations, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
     FROM inserted
 ) AS i
 INNER JOIN tblAffiliation a ON 
     i.Affiliations = a.Affiliations AND
     i.identity_column = a.identity_column
 INNER JOIN tblAffiliationCntr3Cnt ac WITH (ROWLOCK,XLOCK) ON
     ac.Affiliations = a.Affiliations

 UPDATE tblAffiliationCntr3Cnt
 SET Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliations = tblAffiliationCntr3Cnt.Affiliations)

 GO --end of trigger 

Open in new window


Please help and thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
That code isn't mine. You took it from your other question.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Based on my first code (MAX()+1) and in your new table solution, this may work for you:
 
CREATE TRIGGER trgInsAffiliation
ON tblAffiliation AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FOR inserted WHERE Affiliations = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT c.CertID
                 FROM tbl3ACertificateID c
			INNER JOIN inserted i ON c.AffiliationID = i.AffiliationID)
END

Open in new window

Amour22015Author Commented:
But you see I have to deal with 2 tables.

I have to update:
tbl3ACertificateID  --external table
 AffiliationID (PK, int, not null) -- this is for linking to the tblNAsAffiliation
 CertID (int, null) --This is the updated number

And

tblNAsAffiliation --Primary table
 AffiliationID (PK, int, not null) --this is the match
Affiliation --this is to make sure it is only in that folder
Cntr3 --this is where new number is placed

Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID And tblNAsAffiliation.Affiliation = '3A' insert the new value
  from tbl3ACertificateID.CertID into tblNAsAffiliation.Cntr3

Please help and thanks
Amour22015Author Commented:
I am sorry I have that backwards

Trigger is placed over tblNAsAffiliation

and then
 tbl3ACertificate is updated to match.

Please help and thanks
Amour22015Author Commented:
So where I say this:
Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID And tblNAsAffiliation.Affiliation = '3A' insert the new value
   from tbl3ACertificateID.CertID into tblNAsAffiliation.Cntr3

I mean:
Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID And tblNAsAffiliation.Affiliation = '3A' insert the new value
   from tblNAsAffiliation.Cntr3 into tbl3ACertificateID.CertID

Please help and thanks
Amour22015Author Commented:
In your last statement where does tbl3ACertificateID table get updated?

and seeing that this is getting updated then:
tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID

will never happen because there is no matching AffiliationID in tbl3ACertificateID until after it gets updated?

Please help and thanks
Amour22015Author Commented:
Also on your statement I get an error:
Msg 156, Level 15, State 1, Procedure trgInsAffiliation, Line 5
Incorrect syntax near the keyword 'WHERE'.

Please help and thanks
Amour22015Author Commented:
A post that should take about 1 hour,

takes about 1 hour just to get a response.
Then
takes more than 2 days and still no answer
Wow this is experts.com?
Amour22015Author Commented:
Ok I have this:
CREATE TRIGGER trig_Update_Employee
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window


But I still need to add the sequential # + 1
Vitor MontalvãoMSSQL Senior EngineerCommented:
takes about 1 hour just to get a response.
 Then
 takes more than 2 days and still no answer
 Wow this is experts.com?
Please keep in mind that all Experts here are helping people like you for free. We like to help and not even charging nothing but we also have our personal and professional life to fulfill.

I'll be back to this question later when I have more time to spend with it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error was a typo. Sorry about that. Here is the corrected version:
 
CREATE TRIGGER trgInsAffiliation
ON tblNAsAffiliation AFTER INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Affiliations = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT c.CertID
                 FROM tbl3ACertificateID c
			INNER JOIN inserted i ON c.AffiliationID = i.AffiliationID)
END

Open in new window

Amour22015Author Commented:
Vitor Montalvão

I am sorry but I just do not understand the logic.  Where is the counter for Cntr3?  Should there not be a:
Cntr3 = Max(Cntr3) + 1 ?

Maybe I am just missing where it is in the trigger?

Please clarify thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
Should there not be a:
 Cntr3 = Max(Cntr3) + 1 ?
It was in my first version but since you wanted to go for a new table solution then the "Max(Cntr3) + 1" will be the CertID from your new table.
Amour22015Author Commented:
Plus I think I am looking for something like:
CREATE TRIGGER trgInsAffiliation
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window


then all I will need is cntr3 = max(cntr3) + 1 before it inserts into the external table.

Please help and thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
then all I will need is cntr3 = max(cntr3) + 1 before it inserts into the external table
Then what for you'll need the tbl3ACertificateID?
Amour22015Author Commented:
Oh,

Your first version:
CREATE TRIGGER trgInsAffiliation
ON tblAffiliation AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FOR inserted WHERE Affiliations = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')
END

Open in new window


is good but then it does not update the external table.  So I need that also.

We are very close to what I am looking for please don't give up.

Thanks
CREATE TRIGGER trgInsAffiliation
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window

Amour22015Author Commented:
Q.) Then what for you'll need the tbl3ACertifica

A.) My company wants to keep track of the new records added.

If client deletes one of the records and comes back wanting to know what happen we can say yes/no it was never added.

I am just going along with what they say to do and trying to make it happen.

Thanks
Amour22015Author Commented:
Also don't forget:

Cntr3 = Max(Cntr3) + 1
Where Affiliation = '3A'

Thanks
CREATE TRIGGER trgInsAffiliation
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window

CREATE TRIGGER trgInsAffiliation
ON tblAffiliation AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FOR inserted WHERE Affiliations = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')
END

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
If client deletes one of the records and comes back wanting to know what happen we can say yes/no it was never added.
That's why you don't need "MAX()+1" but the value in CertID. It was because of the gaps if I remember good.
If you want post here some sample data so I can explain you with some images.
Amour22015Author Commented:
My thinking is to add the new value to cntr3 and then insert into the external table the new AffiliationID and Cntr3?

But maybe you have another solution?

Also in your first trigger there are errors:
Incorrect syntax near Where expecting ID.

I just tried combining your trigger with the one I just posted.

Please help and thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also in your first trigger there are errors:
 Incorrect syntax near Where expecting ID.
I already posted the correct version. It was a typo (FOR instead of FROM).
Amour22015Author Commented:
Ok I currently have this:
CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
  IF EXISTS(SELECT 1 FROM inserted WHERE Affiliation = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')

    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window

But am getting an error:
Msg 286, Level 16, State 1, Procedure trgInsAffiliation, Line 7
The logical tables INSERTED and DELETED cannot be updated.

Please help and thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
Msg 286, Level 16, State 1, Procedure trgInsAffiliation, Line 7
You don't call a trigger as you call a stored procedure. The trigger is something automatically executed by the SQL Server engine when the operation is executed (an INSERT in this case).
Amour22015Author Commented:
Great Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.