Auto-increment value in insert query

Auto-increment value in insert query

Post by Neil Negandh » Sun, 10 May 1998 04:00:00



I've come across a SQL problem which I can't seem to solve satisfactorily.
What I want to do is something like this:



INSERT a_table (field1, field2, lineno)

FROM a_table

Field1, and lineno make up a_table's primary key.  I need to ensure the
SELECT statement only inserts unique keys.  For example, the original data
could look like this:

Field1, Field2, r_date, lineno
aaa     bbb     5/8/98   1
ccc     bbb     5/8/98   1
xxx     yyy     5/8/98   2

After the insert, I need this:
Field1, Field2, r_date, lineno
aaa     bbb     5/8/98   1
ccc     bbb     5/8/98   1
xxx     yyy     5/8/98   2
xxx     bbb     5/8/98   3
xxx     bbb     5/8/98   4

I can't use an identity column in this case and I'd rather stay away from
cursors.  Anyone have any other ideas?
--
Neil Negandhi

Senior Consultant, BDO Dunwoody

 
 
 

Auto-increment value in insert query

Post by Tony Rogerso » Mon, 11 May 1998 04:00:00


The problem exists because someone else may insert a row from the point you
have retrieved max(lineno) to when you use it on the insert.

I tend to have a table that holds the next primary key for each table.

In your case try,

INSERT a_table( field1, field2, lineno )
SELECT 'xxx', field2, ( SELECT MAX( lineno) + 1 FROM a_table )
FROM a_table

--
Tony Rogerson
Torver Computer Consultants Ltd.


>I've come across a SQL problem which I can't seem to solve satisfactorily.
>What I want to do is something like this:



>INSERT a_table (field1, field2, lineno)

>FROM a_table

>Field1, and lineno make up a_table's primary key.  I need to ensure the
>SELECT statement only inserts unique keys.  For example, the original data
>could look like this:

>Field1, Field2, r_date, lineno
>aaa     bbb     5/8/98   1
>ccc     bbb     5/8/98   1
>xxx     yyy     5/8/98   2

>After the insert, I need this:
>Field1, Field2, r_date, lineno
>aaa     bbb     5/8/98   1
>ccc     bbb     5/8/98   1
>xxx     yyy     5/8/98   2
>xxx     bbb     5/8/98   3
>xxx     bbb     5/8/98   4

>I can't use an identity column in this case and I'd rather stay away from
>cursors.  Anyone have any other ideas?
>--
>Neil Negandhi

>Senior Consultant, BDO Dunwoody


 
 
 

Auto-increment value in insert query

Post by Neil Negandh » Mon, 11 May 1998 04:00:00


Actually, I over-simplified the problem a little.  MAX(lineno) can return
NULL so MAX(lineno) + 1 can also return NULL.  Also, I have to compare
lineno from a_table
to lineno from b_table and select the max.  If I can't do this in a query,
I'm thinking of storing the appropriate lineno somewhere and referring to it
in the query.  It's going to be difficult though, as the SELECT statement
can insert multiple rows.


>The problem exists because someone else may insert a row from the point you
>have retrieved max(lineno) to when you use it on the insert.

>I tend to have a table that holds the next primary key for each table.

>In your case try,

>INSERT a_table( field1, field2, lineno )
>SELECT 'xxx', field2, ( SELECT MAX( lineno) + 1 FROM a_table )
>FROM a_table

>--
>Tony Rogerson
>Torver Computer Consultants Ltd.

 
 
 

Auto-increment value in insert query

Post by Neil Negandh » Mon, 11 May 1998 04:00:00


I thought a bit more about your suggestion and came up with the following:

(select max(lineno) from a_table where employee_id = '100')
union
(select max(lineno) from b_table where employee_id = '100')
union
(select 1)

Now I'm trying to figure out how to wrap a MAX around the whole thing.
If the above is <subquery1> then
SELECT MAX(<subquery1>) FROM a_table
gives a Incorrect syntax near the keyword 'union' error message.
Can this be done?


>The problem exists because someone else may insert a row from the point you
>have retrieved max(lineno) to when you use it on the insert.

>I tend to have a table that holds the next primary key for each table.

>In your case try,

>INSERT a_table( field1, field2, lineno )
>SELECT 'xxx', field2, ( SELECT MAX( lineno) + 1 FROM a_table )
>FROM a_table

>--
>Tony Rogerson
>Torver Computer Consultants Ltd.

 
 
 

Auto-increment value in insert query

Post by David Fernand » Tue, 12 May 1998 04:00:00




>I thought a bit more about your suggestion and came up with the following:

>(select max(lineno) from a_table where employee_id = '100')
>union
>(select max(lineno) from b_table where employee_id = '100')
>union
>(select 1)

>Now I'm trying to figure out how to wrap a MAX around the whole thing.
>If the above is <subquery1> then
>SELECT MAX(<subquery1>) FROM a_table
>gives a Incorrect syntax near the keyword 'union' error message.
>Can this be done?

Create a VIEW as
(select zz=max(lineno) from a_table where employee_id = '100')
union
(select zz=max(lineno) from b_table where employee_id = '100')

Say view1:

From now on you can:

SELECT MAX(zz) FROM view1

Bye
David Fernandes
PH Informatica

 
 
 

Auto-increment value in insert query

Post by Walter Biff » Tue, 12 May 1998 04:00:00


Hi,
I was following your thread because I got kind of the same problem.

I need to do a multiple row insert into a table and would like to create my
own
increment.

from your sample I built the following:

INSERT a1( field_1,myid )
SELECT name, ( SELECT id +
convert(int,SUBSTRING(convert(varchar(10),RAND(datepart(ms,getdate()))),4,3)
) FROM holding )
FROM a2

The insert runs but the myid column gets filled with duplicates. Meaning
this technique seems to work
just if I insert a record at a time.  Does this mean that it not possible to
insert multiple rows?

The myid column from the a1 table is a Primary key therefore duplicate
values are not allowed.



Negandhi"

>>I thought a bit more about your suggestion and came up with the following:

>>(select max(lineno) from a_table where employee_id = '100')
>>union
>>(select max(lineno) from b_table where employee_id = '100')
>>union
>>(select 1)

>>Now I'm trying to figure out how to wrap a MAX around the whole thing.
>>If the above is <subquery1> then
>>SELECT MAX(<subquery1>) FROM a_table
>>gives a Incorrect syntax near the keyword 'union' error message.
>>Can this be done?

>Create a VIEW as
>(select zz=max(lineno) from a_table where employee_id = '100')
>union
>(select zz=max(lineno) from b_table where employee_id = '100')

>Say view1:

>From now on you can:

>SELECT MAX(zz) FROM view1

>Bye
>David Fernandes
>PH Informatica

 
 
 

Auto-increment value in insert query

Post by Neil Negandh » Sat, 23 May 1998 04:00:00


What I did to solve the problem was to create a temporary table consisting
of the key column(s) and an identity column.  I then do an INSERT SELECT
populating lineno with the value of the identity column from the temporary
table.


>Hi,
>I was following your thread because I got kind of the same problem.

>I need to do a multiple row insert into a table and would like to create my
>own
>increment.

>from your sample I built the following:

>INSERT a1( field_1,myid )
>SELECT name, ( SELECT id +
>convert(int,SUBSTRING(convert(varchar(10),RAND(datepart(ms,getdate()))),4,3
)
>) FROM holding )
>FROM a2

>The insert runs but the myid column gets filled with duplicates. Meaning
>this technique seems to work
>just if I insert a record at a time.  Does this mean that it not possible
to
>insert multiple rows?

>The myid column from the a1 table is a Primary key therefore duplicate
>values are not allowed.



>Negandhi"

>>>I thought a bit more about your suggestion and came up with the
following:

>>>(select max(lineno) from a_table where employee_id = '100')
>>>union
>>>(select max(lineno) from b_table where employee_id = '100')
>>>union
>>>(select 1)

>>>Now I'm trying to figure out how to wrap a MAX around the whole thing.
>>>If the above is <subquery1> then
>>>SELECT MAX(<subquery1>) FROM a_table
>>>gives a Incorrect syntax near the keyword 'union' error message.
>>>Can this be done?

>>Create a VIEW as
>>(select zz=max(lineno) from a_table where employee_id = '100')
>>union
>>(select zz=max(lineno) from b_table where employee_id = '100')

>>Say view1:

>>From now on you can:

>>SELECT MAX(zz) FROM view1

>>Bye
>>David Fernandes
>>PH Informatica