SQL Server - Making a counter table

Hi Experts,

I have a vertical table.
 tblAffiliation

 I have a column with a Certificate #:
tblAffiliation.Cntr3

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

It is base on:
Where tblAffiliation.Affiliations = '3A' (Folder)

 The Column:
 Cntr3 (int, null)
 Number starts with 1 - 9999  is current on what is in the tblAffiliation.Cntr3 Column
so something like: Max(Cntr3) + 1?

Trigger is best way to handle this?
 with an external table to keep track of the sequential #?
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.

Here is some data:
Contact1       Contact2       Affiliation      Cntr3
1720                   10631                  3A      1
1720                   10631                  3A      2
      4                   10129                  3A      4
    12                   10134                  3A      12
    17                   10136                  3A      17
    20                   10138                  3A      20
    25                   10139                  3A      25
    26                   10141                  3A      26
    29                   10142                  3A      29
    25                   10140                  3A      31
    26                   10141                  3A      32
    26                   10141                  3A      33


 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.

Scott PletcherSenior DBACommented:
So what specifically are you trying to do?

Just assign the next row to be inserted for '3A' a value of 34?  Or renumber the existing one starting at 1 (:shudder:, bad idea)?

If it's just assign the next value, you don't need a separate table.  You just need an index on:
( affliliations, cntr3 desc )

I can then write you a trigger that will quickly and easily assign the next sequential cntr3 number(s) for row(s) being INSERTed.
Amour22015Author Commented:
Hi,

I am looking to do this:
Max(Cntr3) + 1

Also I should mention on all other folders the Cntr3 column is 0

So the only time that there is a sequential # is based only on the Folder 3A

Q.) Just assign the next row to be inserted for '3A' a value of 34?
A.) Just assign the next row to be inserted for '3A a value of Max(Cntr3) + 1
So take the maximum number and add 1.  Please no renumbering.

Q.) If it's just assign the next value, you don't need a separate table.  You just need an index on:
 ( affliliations, cntr3 desc )
A.) No because the other Folders that are in the table do not have a numbering in Cntr3 plus in that column the other folders currently have 0

Q.) I can then write you a trigger that will quickly and easily assign the next sequential cntr3 number(s) for row(s) being INSERTed.

A.) Yes but it has to be based on Affiliation = 3A (Folder)

I would agree with the trigger a lone but what if a record was deleted?  isn't that the reason for the external table (counter table)?

Thank You for helping.
Scott PletcherSenior DBACommented:
>>  I would agree with the trigger a lone but what if a record was deleted?  isn't that the reason for the external table (counter table)? <<

I don't know.  So rows can be deleted?  And you don't want to reuse a counter if the row with that counter was deleted?  Yes, in that case you must use a separate table.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Amour22015Author Commented:
Q.) So rows can be deleted
A.) yes

Q.) don't want to reuse a counter if the row with that counter was deleted
A.) Yes

So it looks like I need a external table with a trigger.
So columns needed for the external table:
AffiliationID (PK, int, not null) -- this is for linking to the tblAffiliation
Cntr3 (int, null)

with a trigger

Name table: tblAffiliationCntr3Cnt

Thanks
Scott PletcherSenior DBACommented:
I thought the column name was "Affiliations"?

CREATE TABLE tblAffiliationCntr3Cnt (
    Affiliations int PRIMARY KEY,
    Cntr3 int NULL
    )
--initialize the table
INSERT INTO tblAffiliationCntr3Cnt ( Affiliations, Cntr3 )
SELECT Affiliations, MAX(Cntr3) AS Cntr3
FROM tblAffiliation
GROUP BY Affiliations
ORDER BY Affiliations
Scott PletcherSenior DBACommented:
Something like this for the trigger.  I'm fairly sure it will need further testing:

CREATE TRIGGER tblAffiliation__TRG_INSERT
ON tblAffiliation
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @updates TABLE (
   Affiliations varchar(10),
   Cntr3 int,
   PRIMARY KEY(Affiliations, Cntr3)
   )

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version of SQL Server are you using?
Since SQL Server 2012 that Sequences are available and are very easy to use:
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

Amour22015Author Commented:
Hi Vitor Montalyao

So if this:

1,2,3,4,6,7,8,20

So this would use:
5,9.10.11.12.13.14.15.16.17.18.19.21.22........

As the next numbers?

Thanks for helping.
Vitor MontalvãoMSSQL Senior EngineerCommented:
No. Sequence only stores the last value used. It won't fill any gaps so in your example it will store 20 and then the next value will be 21.
Vitor MontalvãoMSSQL Senior EngineerCommented:
But you can always define the next value of a Sequence so it will fill the gap for you:
ALTER SEQUENCE MySequence RESTART WITH 5

Open in new window

Unfortunally you'll need to do it manually until you fill all the gaps.
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 help with this..
Amour22015Author Commented:
Ok so I will need help with 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
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

Please help and thanks
Scott PletcherSenior DBACommented:
I did the complete trigger, adding the numbers and all.  Add a check for " Affiliation = '3A' " in it if you need it.  Obviously I can't actually run the trigger to test it since I don't have those tables.
Amour22015Author Commented:
Hi ScootPletcher,

I have tried your trigger but I get error and some logic is not yet correct:
CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

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

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

 GO --end of trigger

Open in new window


Like I am not doing this and there is no match on both tables to this column:
SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliation = tbl3ACertificateID.Affiliation)

The only match is:
SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE AffiliationID = tbl3ACertificateID.AffiliationID)

Please help and thanks
Amour22015Author Commented:
Plus I do not see anything about:
tblNAsAffiliation.Affiliation = '3A'
CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

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

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

 GO --end of trigger

Open in new window

Amour22015Author Commented:
Also don't I need to first put in the new record into the external table (tbl3ACertificateID)? within the trigger like I mention in: ID: 40810914

Please help and thanks
CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

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

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

 GO --end of trigger

Open in new window

Scott PletcherSenior DBACommented:
I had to guess at some thing because I don't have the table definitions.  I can't magically know what columns your table does or doesn't have.

Does the table have an identity column?  If it indeed does, did you change the:
i.identity_column = a.identity_column
to either:
I.$IDENTITY = a.$IDENTITY
or to the actual column name with the identity?

And, yes, you'll need to do an initial load of the table that stores the max values, for which, again, I've already posted specific code (review my earlier posts):

CREATE TABLE tblAffiliationCntr3Cnt (
     Affiliations int PRIMARY KEY,
     Cntr3 int NULL
     )
 --initialize the table
 INSERT INTO tblAffiliationCntr3Cnt ( Affiliations, Cntr3 )
 SELECT Affiliations, MAX(Cntr3) AS Cntr3
 FROM tblAffiliation
 GROUP BY Affiliations
 ORDER BY Affiliations

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:
Wow the naming in the trigger might be all wrong.

The primary key in the external table (tbl3ACertificateID) is AffiliationID
This is what my external table has in it:
tbl3ACertificateID
 AffiliationID (PK, int, not null) -- this is for linking to the tblAffiliation
 CertID (int, null)

Only 2 columns.

There is no Affiliations column.
There is a Affiliation column and that should equal '3A'
so: tblNAsAffiliation.Affiliation = '3A'

This that you have:
DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

I don't think is correct but I don't understand the logic to change it.

Maybe if @updates = external table (tbl3ACertificateID) then:
DECLARE @updates TABLE (
    AffiliationID varchar(10),
    CertID int,
    PRIMARY KEY(AffiliationID, CertID)
    )
But I am just ? at this point.

You trigger looks good I just need help with the logic.

Please help and thanks.
Amour22015Author Commented:
Q.) Does the table have an identity column?
A. ) No

I did this:
CREATE TABLE tblAffiliationCntr3Cnt (
      Affiliations int PRIMARY KEY,
      Cntr3 int NULL
      )

But changed to:
CREATE TABLE tblAffiliationCntr3Cnt (
      AffiliationID int PRIMARY KEY,
      CertID int NULL
      )

On this:
 INSERT INTO tblAffiliationCntr3Cnt ( Affiliations, Cntr3 )
  SELECT Affiliations, MAX(Cntr3) AS Cntr3
  FROM tblAffiliation
  GROUP BY Affiliations
  ORDER BY Affiliations

I did this:
 INSERT INTO tbl3ACertificateID ( AffiliationID, CertID)
  SELECT AffiliationID, Cntr3
  FROM tblNAsAffiliation
 Where Affiliation = '3A'

So in the external table (tbleACertificateID)
I have all records where tblNAsAffiliation.Affiliation = '3A'
Should I have done this?

Thanks for helping.
Amour22015Author Commented:
I just found out that this:
So in the external table (tbleACertificateID)
 I have all records where tblNAsAffiliation.Affiliation = '3A'
 Should I have done this?


is correct because that way we can keep track of historical data:
If client asks what happened to this AffiliationID number we now have a external table to say yes/no it was not in the table.

Please help and thanks.
Scott PletcherSenior DBACommented:
I was just putting the highest value in there to track it.  All the rows will not work the way the code is written now.  If you need the history, that should go in a different table.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.