Member Avatar for f_atencia

Hi all,
I'm a newbie when it comes to SQL and I have 2 tables that I need to update. Let's say, for argument's sake that the first table (Products) has an identity column (automatically incremented) with the name of ApplicationValueID. It stores a bunch of data about a product for example:

ApplicationValueID |Item  |
-------------------|------|
1                  |Coke  |
2                  |Sprite|

Say I have another table that stores the attributes of the product from the Products table (named ProductAttributes), example:

ApplicationValueID |Attribute |Value   |
-------------------|----------|--------|
1                  |Price     |3.95    |
1                  |Vendor    |CocaCola|
2                  |Price     |3.95    |

Basically I want to create just one SQL statement where I can insert a record for Products table and grab the newly-created ApplicationValueID and insert it into the ProductAttributes table. I was told to use @@IDENTITY, but really have no idea how to use this.

Any help would be greatly appreciated

  • 2 Contributors
  • 1 Reply
  • 104 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by buddylee17
Member Avatar for buddylee17

You can't insert into 2 tables with one statement. You can grab the identity of the parent table and insert into the child table with 2 statements:

DECLARE @AppValId int

INSERT Products (Item)
VALUES ('Pepsi')

SET @AppValId = @@IDENTITY

INSERT ProductAttributes (ApplicationValueID,Attribute,Value)
VALUES (@AppValId,'Price','2.99')
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.