CODEWITHSUNDEEP
t-sql
GurdeepS
GurdeepS

Reputation: 67223

SQL Insert with subquery

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

Upvotes: 0

Views: 838

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 1

obsoleter
obsoleter

Reputation: 355

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.

Upvotes: 0

Related Questions