simple-thomas simple-thomas - 9 months ago 41
SQL Question

Insert composite incremental key

I've searched and searched and can't find an answer on this.

If I have a composite key in mssql and want it to look like such:

k2 k3
1 1
1 9

2 1
2 9
2 3

3 1
3 8

Registries are inserted 1 or more at a time and for each round of inserts, the k2 is the same. K2 must also be incremental.

In this example I inserted the first 2 registries at the same time and so the k2 was set at 1 because there were no registries before and the k3 was given by the user.

The 2nd insert was for 3 registries and k2 was set at 2 because it incremented since the last insert.

The 3rd insert was for 2 registries and it goes on...


Maintain two tables (a and b). Table a has K1 auto increment primary key. Table b has K2 auto increment primary key. Reference table b containing primary key K2 in table a as a foreign key on K2. For each insertion batch, advance table b and insert the scope_identity() / output inserted.K1 value as K2 reference.

Is that what you are looking for?