Generating next primary key value

Question

Let's say I have two tables. The first table is like such

ID      Code
NULL    106164029
NULL    106100797
NULL    106100793
NULL    106301098
NULL    106010735
NULL    206010989
NULL    206010956

Now the second table is like this...

ID  Code
1   102323223
2   105454545
3   106232244
4   106432432
5   106043222
6   206122222
7   211111116

How can I generate the next ID (primary key) value for the table when or after I have merged them together so that my table would look like this?

ID    Code
1     102323223
2     105454545
3     106232244
4     106432432
5     106043222
6     206122222
7     211111116
8     106164029
9     106100797
10    106100793
11    106301098
12    106010735
13    206010989
14    206010956

What have you tried??? Have you tried to just do a simple insert? It doesn't seem like you tried anything. — Eric, Dec 17 '18 at 22:23
I tried to do an insert... was met with this error: ```Cannot insert the value NULL into column 'ID, table 'tempdb.dbo.#templist___________________________________________________________________________________________________________000000205A4D'; column does not allow nulls. INSERT fails. ``` — JoYi, Dec 17 '18 at 22:32
How is the `ID` value in your second table generated? Is it an `IDENTITY` column? Or the `NEXT VALUE FOR` a `SEQUENCE` object? Where that comes from makes all the difference. — Eric Brandt, Dec 17 '18 at 22:41
The 3rd table - is it a separate table, or you just add data from first to second? Does it have `identity` already? Which SQL Server version you use, does it allow to use sequences? — Roger Wolf, Dec 17 '18 at 22:50

S3S · Accepted Answer · 2018-12-17T22:19:58.000

You can use the IDENTITY property for you PK field and can keep the order of the first table, and the give a random order to the second.

You could also specify an order for the table with the NULL id's, but you'd have to choose what that order is with a conditional order by since the table has no logical order based on what you have provided.

create table #t1 (id int null, code bigint)
create table #t2 (id int null, code bigint)

insert into #t1
values
(NULL,106164029),
(NULL,106100797),
(NULL,106100793),
(NULL,106301098),
(NULL,106010735),
(NULL,206010989),
(NULL,206010956)

insert into #t2
values
(1,102323223),
(2,105454545),
(3,106232244),
(4,106432432),
(5,106043222),
(6,206122222),
(7,211111116)

--here is your final table with the auto calculated id primary key
create table #t3 (id int identity (1,1), code bigint)
alter table #t3 add constraint pk primary key (id)

--the order by keeps the same order as the original table with the id values
insert into #t3
select code from #t2
order by id

--since the ID is null in this table, i didn't specify an order by but you could using conditional order by
insert into #t3
select code from #t1


--see the results
select * from #t3 order by id

drop table #t1, #t2, #t3

This returns the following, which has your order for the first table (1-7) but no guaranteed order for the second unless you apply some logic to specify the order of that table. As it is, there is no way to tell. A single clustered index could help.

+----+-----------+
| id |   code    |
+----+-----------+
|  1 | 102323223 |
|  2 | 105454545 |
|  3 | 106232244 |
|  4 | 106432432 |
|  5 | 106043222 |
|  6 | 206122222 |
|  7 | 211111116 |
|  8 | 106164029 |
|  9 | 106100797 |
| 10 | 106100793 |
| 11 | 106301098 |
| 12 | 106010735 |
| 13 | 206010989 |
| 14 | 206010956 |
+----+-----------+

Thanks for the help.. Turns out it was this but I did not need to specify (1,1) or the alter table command — JoYi, Dec 17 '18 at 23:27

score 0 · Answer 2 · answered Dec 17 '18 at 22:13

0

You have three ways.

The first (simple) way: define an identity field, so it fill itself when you execute an INSERT statement.

The second way: When you write your INSERT statement you can apply a logic to calculate your ID, as follow:

INSERT INTO yourtable (id, code)
SELECT ISNULL((SELECT COUNT(*) FROM yourtable t2
       WHERE t2.code < @code), 0), code

The third way: You apply an UPDATE statement after your INSERT.

INSERT INTO yourtable (code) VALUES (yourcode)

and after all INSERT statements you can write as follow:

UPDATE yourtable SET id = 
    ISNULL((SELECT COUNT(*) FROM yourtable t2
    WHERE t2.code < yourtable.code), 0)
WHERE id IS NULL

answered Dec 17 '18 at 22:13

Joe Taras

15,166
7
42
55

They want to maintain the order of their data (though one table has no real order) so for the table that has the ID field populated, an `order by` on insert to your identity column would do this. – S3S Dec 17 '18 at 22:24
1

Jeff Moden will try to kill you if he will happen across your third query. This is a semi-cartesian, it will bring the server to its knees on any reasonable amount of data. – Roger Wolf Dec 17 '18 at 22:42
@scsimon: The order will affect the new data (but in question are the same order of table1) – Joe Taras Dec 17 '18 at 23:12
@RogerWolf: Yes Roger, but I only describe all possible solution – Joe Taras Dec 17 '18 at 23:13

Generating next primary key value

2 Answers2