Using A Specific rows ID as a Variable on Insert SQL

Question

Ok i have 3 tables

Person Item Purchase

Person has 2 columns: PersonId, PersonName Item has 2 columns: ItemId, ItemName Purchase has 3 columns: PurchaseId, ItemId, PersonId

Lets Say Person with a PersonName of "Justin" Has a PersonId of 2 and Item with an ItemName of "Book" Has an ItemId of 4

I would like to insert into the purchase table using the values 2 and 4

What i have so far

 Select PersonId  AS a FROM tblPerson
 Where PersonName = 'Justin'

 Select ItemId  AS b From tblItem
 Where ItemName = 'Book'

 INSERT INTO tblPurchase (ItemId,PersonId)
 VALUES (b,a);

So the new entry in Purchase would be

PurchaseId ItemId PersonId 1 4 2

How would i do this in sql? Would i use variables?

Im using SQL Server Management Studio 2012

T McKeown · Accepted Answer · 2014-01-07 18:49:21Z

1

DECLARE @PersonId INT
DECLARE @ItemId INT

Select @PersonId = PersonId FROM tblPerson
Where PersonName = 'Justin'

Select @ItemId = ItemId From tblItem
Where ItemName = 'Book'

  INSERT INTO tblPurchase (ItemId,PersonId)
  VALUES (@ItemId  ,@PersonId);

If you are using an Identity or auto generated key on tblPurchase you can get that key after the INSERT using:

DECLARE @NewKey INT
SET @NewKey = SCOPE_IDENTITY()

edited Jan 7, 2014 at 18:49

answered Jan 7, 2014 at 18:41

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bogdan Sahlean Over a year ago

I hope there are UNIQUE INDEXes/CONSTRAINTs on dbo.tblPerson.PersonName and on dbo.tblItem.ItemName.

Collectives™ on Stack Overflow

Using A Specific rows ID as a Variable on Insert SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related