Insert composite incremental key

Question

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:

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...

Okay I want you to forget everything about what you want. Then read this question and see if you can figure out what's being asked. Meanwhile the rest of us will pray for enlightenment about k1... — Tony Hopkinson, Jun 27 '13 at 21:53
k1 was irrelevant so I removed it. Read it again, see if it enlightened you up. — simple-thomas, Jun 27 '13 at 22:00
How can I receive something like 1, 4, 9 and insert 3 registries. Each registry will have the same k2 that will be the last k2 inserted on the table + 1 and k3 will have the values I received (1, 4, 9). — simple-thomas, Jun 27 '13 at 22:07

cocogorilla · Accepted Answer · 2016-04-18T05:41:08.363

4

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?

edited Apr 18 '16 at 05:41

answered Jun 27 '13 at 22:10

cocogorilla

1,815
14
36

That is a good idea, I think that is it. Thanks – simple-thomas Jun 27 '13 at 22:13

Insert composite incremental key

1 Answers1