How do I insert a row into a table with only an identity field? 3

[entaroadun]

I have tried every possible version of INSERT statement and can't find anything. Please help!

 

[wilk]

with a three column table col1 would be the identity, for the first go the value of 1 would go into col1 automatically.

insert into test (col2,col3) values ('test','test again')

 

[r937]

you mean the table has only one column, and it's an autonumber?

that's a really neat question, so i tried it

i opened the table in datasheet view, but i couldn't get it to add a row

then i tried an INSERT query and couldn't get it to add a r w either, except for this --

  insert into autonumberonlytable
        values (34)

so you can insert rows by assigning a value to the autonumber, but you can't get it to increment!!

weird

anyhow, i'd be really curious to know what usefulness such a table, if it worked, would be to you

i'd rack it up under the general heading of "don't do that, then" (

http://www.clueless.com/jargon3.0.0/Don't_do_that,_then!.html)

rudy

 

[r937]

my apologies, the previous answer was for ms access, not sql/server, i did not test it under sql/server, and i need to pay more attention to which forum i'm answering in

sorry

 

[entaroadun]

I have a set of two tables which store information about products. I support versioning in my database, which means that I track changes made to product descriptions.

I decided that at a fundamental level, there were two sets of characteristics which belong to a product: those that are time-less, and those bound by time.

ProductID would be time-less.
Name and description would be time-bound.

So I set up two tables:

tblProduct
- productID int IDENTITY

tblProductVersion
- productID int REFERENCES tblProduct
- timestamp
- name varchar(255)
- description text

I wrap all my data access in stored procedures. So I created an SP for adding a new product, which entails:

1. Inserting a row into tblProduct, then getting the @@IDENTITY value.
2. Inserting a row into tblProductVersion with the productID and other characteristics.

So that's why I have a table with only one field. No, I do not want to write my own scheme for assigning productIDs. That's why I chose to use IDENTITY columns. Please don't post any messages telling me my way is wrong.

Does anyone know how to do this insert?

 

[r937]

oh, i would not dream of telling someone so obviously set on one particular way that he is wrong

just add another column to tblProduct, call it foo

then you can say

insert into tblProduct (foo)
    values ('bar')

and your technique will work fine

at a fundamental level, a surrogate key is not the only candidate primary key for a product entity, but i'm not going to tell you that you can't use it, nor that it's wrong

rudy

 

[sunila7]

Hi,

To insert data into a table which has only identity field in it

u can use this SQL statement

insert into #tbl default values

Look for DEFAULT in SQL BOL....

Hope this helps.......

Sunil

 

[wilk]

My appologies for mis understanding the question, before, that is a really good soultion.