Is there a way to get the required information so that I am able to insert records in both the parent and the child...
at the same time and keep the relation in place?
CREATE TABLE dbo.ParentTable (ParentKey uniqueidentifier PRIMARY KEY , DataCol1 varchar(10) , DataCol2 varchar(10) ) GO CREATE TABLE dbo.ChildTable ( ChildKey uniqueidentifier DEFAULT (NEWID()) , ParentKey uniqueidentifier , DataCol3 varchar(10) , DataCol4 varchar(10) ) GO ALTER TABLE dbo.ChildTAble ADD CONSTRAINT FK_ChildTable_ParentTable_ParentKey FOREIGN KEY (ParentKey) REFERENCES dbo.ParentTable(ParentKey) GO
Now to do the insert, the @myNewID is created and used in both inserts:
DECLARE @myNewID uniqueidentifier select @myNewID = NEWID() INSERT INTO ParentTable (ParentKey, DataCol1, DataCol2) VALUES (@myNEWID, 'ABC', 'DEF') INSERT INTO ChildTable (ParentKey, DataCol3, DataCol4) VALUES (@myNEWID, 'GHI', 'JKL') go
Note that ChildTable's primary key is created by the default and is not supplied in the insert statement. It's a different uniqueidentifier. Because we don't need to use ChildKey in any other table it doesn't have to be created by the script.
Do you have comments on this Ask the Expert Q&A? Let us know.
Dig Deeper on Microsoft SQL Server Installation
Related Q&A from Andrew Novick
Can't decide whether to use nullable fields in a table. Development expert Andrew Novick discusses what to do. Continue Reading
If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started. Continue Reading
Development expert Andrew Novick gives you the resources to improve your stored procedure writing skills. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.