Composite PK , And Incrementing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Composite PK , And Incrementing

Author

Topic

Dufresne
Starting Member

5 Posts

Posted - 2010-05-09 : 11:08:58

Hi , I'm newbie for SQL.
I'm stuck with a problem
I've following table


Address 
(
    AddressID   TINYINT NOT NULL,
    CustomerID  INT NOT NULL,
    Address   VARCHAR(200) NOT NULL
)

AddressID and CustomerID are composite Primary Key.
CustomerID is Foreign Key to Customer Table.
What i want is automatic increment AddressID with same CustomerID

Suppose that , we'll add 3 addreses for customer whose id is 1 and 4 addresses for the customer whose id is 2.
So my table shall look like


AddressID CustomerID   Address
1             1        '......'
2             1        'blabla'
3             1        '...'
1             2        '..'
2             2        '....'
3             2        '.....'
4             2        '....'

AddressId shall increment and automatically become 1 when it's entered different CustomerID
How can i do it ? How shall i write my stored procedure?
Btw, I use Microsoft SQL Server 2008.
Thanks in advance

Dufresne
Starting Member

5 Posts

Posted - 2010-05-09 : 15:51:47

Problem is solved.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 16:27:40

You use this approach?
[CODE]INSERFT INTO Address(AddressID, CustomerID)
SELECT AddressID = Dt.n, D.CustomerID
FROM (SELECT 1, 3 UNION
SELECT 2, 4) D(CustomerID, Times)
CROSS JOIN
(SELECT TOP 125 ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)D1(n)
,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)D2(n)
,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)D3(n))Dt(n)
WHERE Dt.n <= D.times;[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources