SQL Insert with subquery

Question

I have a sql statement which is a routine insert of two values into a table (values meaning fields). These are ints/nvarchars.

However, for the 3rd field of the table (which can be null), I want to insert the max number of rows in the table + 1. How can I do this?

EDIT:

Table is as such

Postcode  Active ID
NULL       1      14

I can help to you, but please, write a sample of data: T1, T2 an T3. — dani herrera
– dani herrera, Commented Oct 6, 2011 at 13:19
This sounds like a "roll-your-own" identity field, which is a BAD IDEA — JNK
– JNK, Commented Oct 6, 2011 at 13:19
@JNK perhaps he wants to store the number of rows (which isn't necessarily the same as the max identity value) at the time of the insert. — 3Dave
– 3Dave, Commented Oct 6, 2011 at 13:27
What do you mean by "max number of rows"? Do you want to store the current rowcount in a field at the time of the insert? — Mikael Eriksson
– Mikael Eriksson, Commented Oct 6, 2011 at 13:28
@DavidLively - his edit tells us that's an ID field which seems to support my guess — JNK
– JNK, Commented Oct 6, 2011 at 13:30

Mikael Eriksson · Accepted Answer · 2011-10-06 14:58:49Z

1

This code shows you how it can be done and what trouble you can get into if you need ID to be a unique value in the table. There are more situations where you might end up with duplicates. One would be that two users add rows to the table at the same time.

declare @T table(Postcode varchar(5), Active bit, ID bigint)

insert into @T (Postcode, Active, ID)
select '1111', 1, count(*)+1
from @T

insert into @T (Postcode, Active, ID)
select '2222', 0, count(*)+1
from @T

delete from @T where Postcode = '1111'

insert into @T (Postcode, Active, ID)
select '3333', 0, count(*)+1
from @T

select *
from @T

Result:

Postcode Active ID
-------- ------ --------------------
3333     0      2
2222     0      2

If you need the values to be unique you should use an identity column instead. Perhaps even make it a primary key or at least add a unique constraint on the ID column.

edited Oct 6, 2011 at 14:58

answered Oct 6, 2011 at 13:33

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

obsoleter · Accepted Answer · 2011-10-06 13:31:16Z

0

I'm assuming you are working with SQL Server, since you have not specified otherwise.

INSERT INTO Table_1 (col1, col2, col3)
        VALUES  (15, 'info', 1 + (SELECT COUNT(*) FROM Table_1))

You should know that this will add to the expense of the insertion operation.

answered Oct 6, 2011 at 13:31

obsoleter

3552 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Insert with subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related