hw cn i auto generate Primary key?

Question

hw cn i auto generate Primary key?

swati pathak

SSC Rookie

Points: 47
More actions
December 28, 2005 at 7:44 am

#167555

i am selecting data from 2 tables.to put into 3rd tab.
now in this 3rd table...1st field is Id (PK). hw can i auto generate this Id while insering other filds from another query (select) ???????

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Finlayson SSC-Addicted Points: 470 More actions · Answer 1

If your table is in SQL Server, then you need to set the data type of your primary key field to be an Identity. This will assign a new number (integer) to each row that is inserted into the table automatically. You will not include this field in the insert query that you are running from the other two tables.

If the data is stored in MS Access, then you need to set the data type of the primary key to Autonumber. This will have the same effect as above.

Check identity in Books Online or Autonumber in MS Access help.

Cheers

David

swati pathak SSC Rookie Points: 47 More actions · Answer 2

hi david thanx a lot for ur reply...one more small doubt, actually i am taking some column from table A and B and inserting this rows into table C.(i m nt creating any table)

Now this table C is the one which will auto generate a Id (PK), whenever a new recored is inserted.

INSERT INTO AttorneyMaster(AID,AttorneyTypeId,AttorneyFName,AttorneyMName,......)

values (IDVAL,AttorneyTypeId,AttorneyFName,AttorneyMName,.....); the highlited field is auto generated when remeining data comes from other tables. So as u said------> bigint identity(1,1)..........will work with "Insert" statement??????

Swati

The Aethyr Dragon SSChasing Mays Points: 636 More actions · Answer 3

Hi,

You don't insert into the column at all if it is set as IDENTITY or AUTONUMBER so your statement would look like this:

INSERT INTO AttorneyMaster(AttorneyTypeId,AttorneyFName,AttorneyMName,......)

values (AttorneyTypeId,AttorneyFName,AttorneyMName,.....);

Cheers

The Aethyr Dragon

Cape Town
RSA

swati pathak SSC Rookie Points: 47 More actions · Answer 4

thank you all a lots..its done, My code is working correctly...

Regs,

swati