Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Insert multiple parent-child records
/************************************************************ This is a demonstration on how to insert multiple parent records and multiple child records in the same stored procedure. First version published on stackoverflow and contains an explanation: https://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage Second version with some more explanations can be found on this stackoverflow answer: https://stackoverflow.com/a/38217498/3094533 ************************************************************/ -- Table setup CREATE TABLE Parent ( Parent_Id int IDENTITY(1,1) CONSTRAINT PK_Parent PRIMARY KEY, Parent_Name varchar(10) NOT NULL CONSTRAINT UC_Parent_Name UNIQUE ); CREATE TABLE Child ( Child_Id int IDENTITY(1,1) CONSTRAINT PK_Child PRIMARY KEY, Child_Name varchar(10) NOT NULL CONSTRAINT UC_ChildName UNIQUE, Child_ParentId int CONSTRAINT FK_Child_Parent REFERENCES Parent(Parent_Id) ); GO -- User defined table types setup CREATE TYPE udt_Parent AS TABLE ( Parent_Id int, Parent_Name varchar(10) ); CREATE TYPE udt_Child AS TABLE ( Child_ParentId int, Child_Name varchar(10) ); GO -- The procedure CREATE PROCEDURE InsertMultipleParentsAndChildren ( @Parents dbo.udt_Parent readonly, @Children dbo.udt_Child readonly ) AS DECLARE @Map AS TABLE ( TempId int, InsertedId int ) -- Using merge instead of a regular insert statement allows us to use both sorce and target values in the output clause MERGE INTO Parent USING @Parents AS temp ON 1 = 0 -- Always not matched WHEN NOT MATCHED THEN INSERT (Parent_Name) VALUES (temp.Parent_Name) OUTPUT temp.Parent_Id, inserted.Parent_Id INTO @Map (TempId, InsertedId); INSERT INTO Child(Child_Name, Child_ParentId) SELECT Child_Name, InsertedId FROM @Children as c INNER JOIN @Map as m ON(c.Child_ParentId = m.TempId) GO
run
|
edit
|
history
|
help
0
Sql Server sys.syslanguages
Running Total
№5
SQL Stringdata
prog1
Project 1
new problem SQL
SQL Server - Multiple Holidays Model
Libros
COMPUTED COLUMN