Identity Column

[Aads]

Hi there,

I have a table with one Identity column & there are no other columns. How do I issue the INSERT statement as I need the Id generated to be passed in other tables? Any help would be appreciated.


Thanx,
Aads

[Delerna]

You cannot include a value for an identity column in an insert query. Thats the whole point of an identity column, it generates its own value when you insert.
Why on earth you would want to have a table with a single column that is an identity column I do not understand. The identity column should be associated with real data and therefore extra columns.

Anyway, its your database so...
1) add a dummy column so that you can insert rows using that column. Field type=Bit
or
2) remove the entity property from the column and add 1 to the max value when inserting.
for example, to insert 100 rows
[code=sql]
declare @count int
set @count=0
while @count<99
begin
insert into yourtable set field=(select max(field)+1 from yourtable)
set@count=@coun t+1
end
[/code]
regards

[Delerna]

Actually on second thoughts a more efficient 2nd method would be
[code=sql]
declare @count int
declare @NextValue bigint
set @NextValue=(sel ect max(field)+1 from yourtable)
set @count=@NextVal ue+99
while @NextValue<@cou nt
begin
insert into yourtable set field=@NextValu e
set @NextValue=@Nex tValue+1
end

[/code]

[Aads]

Thanx a lot Delerna for your quick reply.

I suppose you cannot use the SET keyword with the INSERT statement.

Anywayz, I have found the solution.

If you have a table with only one column which happens to be the Identity column & if you would like to issue the INSERT statement you can do so by executing:

INSERT into tblTest DEFAULT VALUES


Regards,
Aads