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
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 explains the simplest way to store an image in SQL Server. Continue Reading
Expert Andrew Novick reveals how to execute a store procedure in one database look at tables in another database. Continue Reading