By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Identity Column

P: 48
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.

Jun 16 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,134
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
2) remove the entity property from the column and add 1 to the max value when inserting.
for example, to insert 100 rows
Expand|Select|Wrap|Line Numbers
  1. declare @count int
  2. set @count=0
  3. while @count<99
  4. begin
  5.    insert into yourtable set field=(select max(field)+1 from yourtable)
  6.    set@count=@count+1
  7. end
Jun 16 '08 #2

Expert 100+
P: 1,134
Actually on second thoughts a more efficient 2nd method would be
Expand|Select|Wrap|Line Numbers
  1. declare @count int
  2. declare @NextValue bigint
  3. set @NextValue=(select max(field)+1 from yourtable)
  4. set @count=@NextValue+99
  5. while @NextValue<@count
  6. begin
  7.    insert into yourtable set field=@NextValue
  8.    set @NextValue=@NextValue+1
  9. end
Jun 16 '08 #3

P: 48
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:


Jun 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.