درج Parent Child در SQL Server با OUTPUT

SQL Server: درج همزمان رکوردهای Parent  , Child با یک دستور کارآمد (با استفاده از OUTPUT)

هنگام کار با پایگاه داده، یکی از وظایف رایج، درج رکوردهای مرتبط در چندین جدول است. سناریوی متداول، درج یک رکورد در جدول Parent و سپس استفاده از شناسه تولید شده برای درج رکوردهای Child مربوطه است. این کار به طور سنتی شامل دو مرحله مجزا بود: ابتدا درج در جدول Parent و سپس بازیابی شناسه تولید شده آن (مثلاً با استفاده از `SCOPE_IDENTITY()`) و سپس درج در جدول Child.

برای درک بهتر، ابتدا جداول زیر را ایجاد می‌کنیم:

CREATE TABLE Parent (
    ParentId INT IDENTITY(1,1) PRIMARY KEY,
    ParentName VARCHAR(100)
);

CREATE TABLE Child (
    ChildId INT IDENTITY(1,1) PRIMARY KEY,
    ParentId INT REFERENCES Parent(ParentId),
    ChildName VARCHAR(100)
);

روش سنتی برای درج یک رکورد Parent و Child، شامل اجرای دو دستور است. پس از درج رکورد Parent، نیاز داریم تا شناسه جدید تولید شده برای آن را بازیابی کنیم. این کار معمولاً با استفاده از تابعی مانند `SCOPE_IDENTITY()` انجام می‌شود که آخرین شناسه تولید شده در محدوده فعلی را برمی‌گرداند.

INSERT INTO Parent (ParentName)
VALUES ('My Parent');

DECLARE @NewParentId INT;
SET @NewParentId = SCOPE_IDENTITY();

INSERT INTO Child (ParentId, ChildName)
VALUES (@NewParentId, 'Child 1'),
       (@NewParentId, 'Child 2');

این رویکرد، اگرچه کارآمد است، اما شامل اجرای دو دستور جداگانه و یک متغیر کمکی می‌شود. SQL Server راه حل بهتری با استفاده از بند `OUTPUT` ارائه می‌دهد که به شما امکان می‌دهد نتایج یک دستور `INSERT`، `UPDATE` یا `DELETE` را بلافاصله بازیابی کنید. این بند به شما اجازه می‌دهد تا داده‌های درج شده (از جمله شناسه‌های تولید شده) را به یک متغیر جدولی، جدول موقت یا حتی به خروجی معمولی ارسال کنید.

استفاده از بند `OUTPUT` به شما این امکان را می‌دهد که درج رکوردهای Parent و Childرا در یک دستور واحد مدیریت کنید، که هم کد را تمیزتر می‌کند و هم می‌تواند در سناریوهای خاصی عملکرد را بهبود بخشد.

مثال 1: درج رکوردهای Parent و Childبا استفاده از OUTPUT

برای نشان دادن چگونگی استفاده از بند `OUTPUT` برای درج Parent و Child، از همان ساختار جدول `Parent` و `Child` استفاده می‌کنیم. در این روش، ما از یک متغیر جدولی (table variable) برای ذخیره شناسه‌های تولید شده Parent استفاده می‌کنیم و سپس این شناسه‌ها را به صورت مستقیم برای درج فرزندان به کار می‌بریم.

DECLARE @InsertedParentIds TABLE (
    ParentId INT,
    ParentName VARCHAR(100)
);

INSERT INTO Parent (ParentName)
OUTPUT INSERTED.ParentId, INSERTED.ParentName
INTO @InsertedParentIds
VALUES ('First Parent');

INSERT INTO Child (ParentId, ChildName)
SELECT i.ParentId, 'First Child 1'
FROM @InsertedParentIds i
WHERE i.ParentName = 'First Parent'; -- این شرط برای سناریوهای پیچیده‌تر با چند Parent در یک اجرا مفید است

این رویکرد، درج Parent و فرزندان آن را در دو دستور `INSERT` جداگانه انجام می‌دهد، اما با استفاده از `OUTPUT` برای مدیریت شناسه‌ها، نیاز به `SCOPE_IDENTITY()` را از بین می‌برد. نکته کلیدی این است که `OUTPUT` نتایج را به یک متغیر جدولی ارسال می‌کند که بلافاصله قابل استفاده است.

مثال 2: درج چندین رکورد Child برای یک Parent

اگر نیاز به درج چندین رکورد Child برای یک رکورد Parent داشته باشیم، می‌توانیم از ساختاری مشابه با مثال قبلی استفاده کنیم، اما با تطبیق بخش `INSERT INTO Child` برای مدیریت ردیف‌های متعدد.

DECLARE @InsertedParentIds TABLE (
    ParentId INT
);

INSERT INTO Parent (ParentName)
OUTPUT INSERTED.ParentId INTO @InsertedParentIds
VALUES ('Second Parent');

INSERT INTO Child (ParentId, ChildName)
SELECT i.ParentId, c.ChildName
FROM @InsertedParentIds i
CROSS JOIN (VALUES ('Second Child A'), ('Second Child B'), ('Second Child C')) AS c(ChildName);

در این مثال، `CROSS JOIN` با `VALUES` به ما اجازه می‌دهد که برای هر `ParentId` درج شده، چندین رکورد Child ایجاد کنیم.

مثال 3: درج رکوردهای Child برای یک Parent موجود

گاهی اوقات، شما یک رکورد Parent از قبل دارید و فقط نیاز به درج رکوردهای Child جدید برای آن Parent خاص دارید. در این حالت، شما نیازی به استفاده از بند `OUTPUT` برای بازیابی `ParentId` ندارید، زیرا `ParentId` از قبل مشخص است.

-- فرض کنید ParentId 10 برای 'Existing Parent' از قبل وجود دارد
DECLARE @ExistingParentId INT = 10; 

INSERT INTO Child (ParentId, ChildName)
VALUES (@ExistingParentId, 'Child for existing Parent 1'),
       (@ExistingParentId, 'Child for existing Parent 2');

این سناریو مستقیم‌تر است زیرا تمرکز فقط بر روی درج فرزندان با یک `ParentId` مشخص است.

مثال 4: استفاده از جدول موقت (Temporary Table) با OUTPUT

در برخی موارد، ممکن است نیاز داشته باشید نتایج `OUTPUT` را به جای یک متغیر جدولی، به یک جدول موقت ارسال کنید. جداول موقت برای ذخیره‌سازی موقت داده‌ها در طول یک جلسه SQL Server مفید هستند و می‌توانند برای عملیات پیچیده‌تر که نیاز به شاخص‌گذاری یا عملیات بیشتر روی داده‌های میانی دارند، کارآمدتر باشند.

CREATE TABLE #TempInsertedParents (
    ParentId INT,
    ParentName VARCHAR(100)
);

INSERT INTO Parent (ParentName)
OUTPUT INSERTED.ParentId, INSERTED.ParentName
INTO #TempInsertedParents
VALUES ('Third Parent');

INSERT INTO Child (ParentId, ChildName)
SELECT t.ParentId, 'Third Child X'
FROM #TempInsertedParents t
WHERE t.ParentName = 'Third Parent';

DROP TABLE #TempInsertedParents;

توجه داشته باشید که پس از اتمام کار با جدول موقت، بهتر است آن را با `DROP TABLE` حذف کنید، هرچند که در پایان جلسه به طور خودکار حذف می‌شود.

مثال 5: درج در چندین جدول Child

یکی از قدرت‌های بند `OUTPUT` این است که می‌تواند برای هماهنگ کردن درج در چندین جدول Child نیز استفاده شود، به شرطی که هر جدول Child به همان رکورد Parent ارجاع دهد.

-- فرض کنید جدول Child2 نیز وجود دارد که به ParentId ارجاع می‌دهد
CREATE TABLE Child2 (
    Child2Id INT IDENTITY(1,1) PRIMARY KEY,
    ParentId INT REFERENCES Parent(ParentId),
    Child2Description VARCHAR(100)
);

DECLARE @InsertedParentIds TABLE (
    ParentId INT
);

INSERT INTO Parent (ParentName)
OUTPUT INSERTED.ParentId INTO @InsertedParentIds
VALUES ('Fourth Parent');

INSERT INTO Child (ParentId, ChildName)
SELECT i.ParentId, 'Fourth Child A'
FROM @InsertedParentIds i;

INSERT INTO Child2 (ParentId, Child2Description)
SELECT i.ParentId, 'Description for Fourth Child 2'
FROM @InsertedParentIds i;

با استفاده از `OUTPUT` به یک متغیر جدولی، می‌توانیم `ParentId` تولید شده را به راحتی برای درج در چندین جدول Child به کار ببریم، و اطمینان حاصل کنیم که همه رکوردهای Child به رکورد Parent صحیح مرتبط هستند. این تکنیک، فرآیند درج رکوردهای Parent و Child  را بسیار کارآمدتر و منظم‌تر می‌کند.