I have a table that looks like this:
| PK | Col Name | Type |
|----+----------+------|
| X | ParentId | int
| X | Id | int
I tried to set Id
to be the identity, and ParentId is set on the parent table. What I was hoping the data would look like would be:
| ParentId | Id |
|----------+----|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
but instead it looks like:
| ParentId | Id |
|----------+----|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 6 |
which makes sense in hindsight. Is the desired effect achievable in some way?
If you want to get the desired output from only the
ParentId
, i also suggest this, you can use this :SQL HERE
But if you still want to use in the table, you can create a
INSTEAD OF INSERT
trigger on your table, here is the trigger you can use :Simplified version of the above trigger
But this one will not work for batch insert like :