CODEWITHSUNDEEP
sqldatabasesql-server-2008
Christer Johansson
Christer Johansson

Reputation: 337

Insert relational data with last id into SQL Server 2008

I have a movie database, this database contains several tables with columns and I am trying to insert new movies with relational data. I am trying to figure out how to put a movie into the database, and and set a specific store where the movie is physically located. Think of a movie rental system, where you want to search a specific movie and find what stores have this particular movie in stock.

I want to insert a new movie, and set stock level to a existing store id. I should be able to search this movie later, and be able to read stocklevels at each store ID.

This is one of my sql queries that I have tried so far,

BEGIN TRANSACTION
    INSERT INTO [dbo].[movies]
           ([titel], [rating], [genre], [releasedatum],
            [land], [sprak], [DVD_Bluray], [imdb_id])
    VALUES
          ('Resident Evil 2', 4, 'Action', '2016-06-10',
           'USA', 'Engelska', 'DVD', '123456987')

SELECT prodID 
FROM dbo.movies

INSERT INTO dbo.lagerstatus_butiker (butiksid) VALUES (35)

COMMIT TRANSACTION

My table Movies contains information about the movie, and the primary key for each movie is prodID which is auto incremented, lagerstatus_butiker is stocklevel for each store. Butiksid is storeID and is a primary key in a separate table called 'butiker' (stores in swedish). I have multiple stores, with unique primary key ID's.

How do I insert a new row into Movies, and at the same time make it belong to a specific store with a stock level?

My movies table look like this:

[titel]
[writer]
[rating]
[genre]
[prodID] <--- Primary key
[releasedatum]
[land]
[sprak]
[DVD_Bluray]
[imdb_id]
[director_id] <--- Foreign key
[butik_id] <--- Foreign key
[lagerstatus]
[actorID] <-- Foreign key

My lagerstatus (stock level) table looks like this:

[butiksid] <-- Foreign key
[prodID] <-- Foreign key
[lagerstatus]

And finally, my butiker (stores) table looks like this:

[butik_namn]
[butiksid] <-- Primary key
[gatuadress]
[postnr]
[postort]
[telnr]

Feel free to suggest improvements and changes, I am trying to learn this in SQL Server 2008. This is only for educational purposes.

Thank you! :)

Upvotes: 1

Views: 285

Answers (1)

marc_s
marc_s

Reputation: 755083

When you insert a row into a table with an IDENTITY column, you can grab that newly created identity value like this:

BEGIN TRANSACTION
    INSERT INTO [dbo].[movies]
           ([titel], [rating], [genre], [releasedatum],
            [land], [sprak], [DVD_Bluray], [imdb_id])
    VALUES
          ('Resident Evil 2', 4, 'Action', '2016-06-10',
           'USA', 'Engelska', 'DVD', '123456987')

    DECLARE @NewID INT

    -- get newly created ID
    SELECT @NewID = SCOPE_IDENTITY();

and now you can use that ID to fill the other table(s):

INSERT INTO dbo.lagerstatus_butiker (butiksid) VALUES (@NewID)

COMMIT TRANSACTION

Upvotes: 2

Related Questions