CODEWITHSUNDEEP
sql-serverjoinprimary-key
ghost_king
ghost_king

Reputation: 930

Database Integrity on 3 Tables

I have 3 tables

There are:

How would I insert a project that has a bug (s) and then bug Logs on bugs efficiently into these tables?

Thanks

Upvotes: 0

Views: 57

Answers (2)

Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

Well here you need to write individual insert statements. Joins will only be used when querying the data..

You can do the following if bugid is an identity column:

DECLARE @bugid bigint
INSERT INTO Bugs (Projectid,other COLUMNS...)
VALUES (values1,VALUES....)

SELECT @bugid= SCOPE_IDENTITY()

INSERT INTO BugLogs(Bugid,other COLUMNS...)
VALUES(@bugid,....)

Alternatively you can use Output clause to get the bugid. This will work in all scenarios:

DECLARE @bugid bigint
INSERT INTO Bugs (Projectid,other COLUMNS...)
OUTPUT INSERTED.BugId INTO @bugid
VALUES (values1,VALUES....)

INSERT INTO BugLogs(Bugid,other COLUMNS...)
VALUES(@bugid,....)

Upvotes: 3

Aheho
Aheho

Reputation: 12821

Joins have nothing to do with Inserts. Joins only come into play when you want to query the data.

If you have your declarative referential integrity in place, then you are going to have to insert records in the following order: Project, Bugs, BugLogs.

If you are working in Microsoft SQL Server, and are using identity columns, after you insert a row, you can use the scope_identity() function to retrieve the primary key assigned and use that to set you foreign keys.

Upvotes: 2

Related Questions