insert (maxID)+1 ??? 1

[level1]

Culd you please let me know any function that allows me to insert a new record that one of its fields must be something like the New records ID +(a Variable)

I tried this but doesnt work:

INSERT INTO pictures
(bandid, production, picfile, piccomments, pictype, username, dateadded)
VALUES
(1, 7, 4,(SELECT MAX(picid) + 1 FROM pictures), 'covers','kthulu', getdate())

In other words i need to get the new id in another column in the first place plus some text which i can easily add if i had the id that i want.

I hope you understand my question.

regards

 

[SQLSister]

if you are always just incremeting by 1 why not autogenerate the number with an identity field?

 

[sunila7]

Hi,

is this wht u r looking for...

INSERT INTO pictures
(bandid, production, picfile, piccomments, pictype, username, dateadded)
Select
1, 7, 4,MAX(picid) + 1 , 'covers','kthulu', getdate() FROM pictures


Sunil

 

[level1]

excellent and works excactly as i wanted, thanks to both of you but the star should go to sunila7 thanks again people.

 

[SemperFiDownUnda]

Level1 Mind you that you can get an error with this. If you have 2 statement run at the same time it will be possible to have them both generate the same ID. This is a rare occurance but can happen

To see this in action make this table
Create table dbo.MyTest
(
ColID INT,
sData CHAR(5)
)
then run this script in about 5 or 6 windows
DECLARE @i INT
SET @i = 1
WHILE @i < 100000
BEGIN
INSERT INTO MyTest SELECT MAX(ColID) + 1, 'aaaa' FROM MyTest
SET @i = @i + 1
END

to add more proof, change 'aaaa'to a different letter sequence for each connection. then run
at the end do

SELECT ColID, COUNT(*)
From MyTest
GROUP BY COLID
HAVING COUNT(*) > 1

Here is just the first 10 repeated IDs (not in order) in my select....notice one of them show that all 6 of my connnections added a record with the same ID into the table.

21777 2
24886 2
28895 2
24039 2
28840 2
18601 5
29048 2
20626 2
17858 6
15532 4

Rule : DON'Tuse MAX(..) + 1 to seed a primary or unique key