0

Is there a way to insert a new record to a table that has an non-unique id and set this id to the next number in the same SQL statement?

Something like

INSERT INTO T1 (id, fname, lname)
VALUES ([last id + 1], 'Dan', 'Thomson');
ehh
  • 3,412
  • 7
  • 43
  • 91
  • Use a WHILE loop with a counter variable? – Alrighty then Nov 02 '16 at 07:10
  • @Alrightythen, it is not clear, can you please explain – ehh Nov 02 '16 at 07:17
  • posted a sample as an answer below. – Alrighty then Nov 02 '16 at 07:21
  • 3
    This is not normal, your table should have a real id (primary key), the best is to add a column autoincremented. Of course you can also keep your old id if necessary. But use it as id is a bad idea because so many people add a line at the same time they will have potentially made the same id. – Esperento57 Nov 02 '16 at 11:36
  • Of course you are right, I just put an example of what I need. ID is not really the field I am using for this concern. This may be a bad example. Thanks – ehh Nov 04 '16 at 12:55

4 Answers4

2

this probably works

INSERT INTO T1 (id, fname, lname)
VALUES (ifnull((select max(id) from T1),0) + 1, 'Dan', 'Thomson')
lamLam
  • 455
  • 2
  • 8
0
DECLARE @COUNTER INT;
SET @COUNTER = 1;

WHILE(@COUNTER <= XXX)
BEGIN

INSERT INTO T1 (id, fname, lname)
VALUES (@COUNTER, @FNAME , @LNAME);

SET @COUNTER = @COUNTER + 1;

END
Alrighty then
  • 127
  • 1
  • 11
0

Built-in function MAX :

INSERT INTO T1 (id, fname, lname)
SELECT ifnull(MAX(id)+ 1,1), 'Dan', 'Thomson' FROM T1

Insert and set value with max()+1 problems

SELECT MAX(col) +1 is not safe -- it does not ensure that you aren't inserting more than one customer with the same customer_id value, regardless if selecting from the same table or any others.

For performance, add an index:

CREATE INDEX T1_INDEX1 ON T1 (id) DESC

References:

Unit Test

-- Create Table
SET SCHEMA QTEMP;
CREAT TABLE testtbl (
    id integer not null default,
    fname char(10) not null default,
    lname char(10) not null default)
;
-- Initialize Table with Data
insert into 
testtbl ( id , fname, lname)
values
( 1, 'fname1', 'lname_1'),
( 2, 'fname2', 'lname_2'),
( 2, 'fname3', 'lname_3'),
( 3, 'fname4', 'lname_4')
;
-- Test Insert Statement
INSERT INTO 
testtbl ( id, fname, lname )
SELECT ifnull(MAX( id ) + 1, 1), 'Dan', 'Thomson' FROM testtbl;
--Confirm Expectation
select * from testtbl;

ID           FNAME       LNAME       
-------------------------------------
1            fname1      lname_1     
2            fname2      lname_2     
3            fname4      lname_4     
4            Dan         Thomson
Community
  • 1
  • 1
Christoff Erasmus
  • 925
  • 1
  • 10
  • 26
0

if you want insert all content of table with unique id start with max +1

INSERT INTO T1 (id, fname, lname)
select
rownumber() over() + ifnull((select max(T1.id) from T1), 0),
T2.zone1, T2.zone2 
from T2
Esperento57
  • 16,521
  • 3
  • 39
  • 45