CODEWITHSUNDEEP
sqlsql-serverdatabase-administration
Denis
Denis

Reputation: 12087

How to increment identity column in pair with another column?

Suppose I want to create a table in SQL Server 2008 R2 as follows (this is probably wrong but it helps illustrate the point):

CREATE TABLE BANK_ACCOUNT
        (
            BankID INT NOT NULL,
            RecID INT IDENTITY(1,1) NOT NULL,
            IDValue varchar(100) NOT NULL,
        )  ON [PRIMARY]
GO

ALTER TABLE BANK_ACCOUNT
    ADD CONSTRAINT
                PK_BANK_ACCOUNT PRIMARY KEY CLUSTERED 
                (
                BankID,
                RecID
                ) ON [PRIMARY]

GO How can I make it so that every time I insert a record into the table the RecID is incremented appropriately with BankID. So for example:

INSERT (BankID, IDValue) INTO BANK_ACCOUNT VALUES (1, "Val1")
INSERT (BankID, IDValue) INTO BANK_ACCOUNT VALUES (1, "Val2")
INSERT (BankID, IDValue) INTO BANK_ACCOUNT VALUES (2, "Val3")
INSERT (BankID, IDValue) INTO BANK_ACCOUNT VALUES (1, "Val4")

Should produce:

BankID | RecID | IDValue
   1      1        Val1
   1      2        Val2
   2      1        Val3
   1      3        Val4

Upvotes: 0

Views: 102

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

instead create a View

CREATE TABLE BANK_ACCOUNT
  (
     Iden_Col INT IDENTITY(1, 1) NOT NULL,
     BankID   INT NOT NULL,
     IDValue  VARCHAR(100) NOT NULL,
  )

GO

INSERT INTO BANK_ACCOUNT
VALUES      (1,'Val1')

INSERT INTO BANK_ACCOUNT
VALUES      (1,'Val2')

INSERT INTO BANK_ACCOUNT
VALUES      (2,'Val3')

INSERT INTO BANK_ACCOUNT
VALUES      (1,'Val4')

go

CREATE VIEW BANK_ACCOUNT_View
AS
    SELECT Row_number()
            OVER(
                partition BY BankID
                ORDER BY Iden_Col) RecID,
            BankID,
            IDValue
    FROM   BANK_ACCOUNT 

select * FROM BANK_ACCOUNT_View

Upvotes: 1

Related Questions