T-SQL Core Question

Question

Let's say I have three tables implemented with a many-to-many relationship. Something like: Person (personID), PersonMovies (personID, movieID) and movies (movieID). What is the correct way to make multiple attachments in sql server? I would like to insert a person, films, and then get all the films that a person owns. So will it be three inserts inside a transaction? If this is the case, I would suggest that the easy part inserts a person and a movie into the table, but how do I insert PersonMovies into the table, since this table relies on the existing identifier in the other two tables. I assume that I will insert into Person and Movies, and then somehow assign the identifier of the newly inserted tables to a variable from these two tables, and then use these variables to insert into the bridge table. I have no idea, but I hopethat it makes any sense since I am VERY confused by this !!

Answer 1

Start by inserting the Person record and use SCOPE_IDENTITY to get a unique identifier if the record is inserted. Then you can use this to insert human movies. Before you can insert the movie People, you need to see if it exists or not using IF EXISTS. If he makes SELECT from an existing table and assigns it a unique variable identifier. If it does not already exist, use the same method to add the person and insert the movie, and then assign SCOPE_IDENTITY to the movie variable.

PL/SQL UPSERT, . , UPSERT T/SQL , .

IF EXISTS (SELECT id FROM dbo.sysobjects WHERE name = 'fts_upsert_team') DROP PROCEDURE fts_upsert_team
GO

CREATE PROCEDURE fts_upsert_team
    @teamID INT OUTPUT,
    @name VARCHAR(100)

AS

    UPDATE 
        fts_teams
    SET
        name = @name
    WHERE
        teamID = @teamID

    IF @@ROWCOUNT = 0
    BEGIN

        INSERT INTO fts_teams
        (
            name
        )
        VALUES
        (
            @name
        )

        SET @teamID = SCOPE_IDENTITY()

    END


GO

Answer 2

, . , , . scope_identity(), . , , PersonMovies, .