روش های درج داده همزمان(Multiple Table Insert) در چند جدول SQL Server

روش‌های کارآمد درج داده همزمان(Multiple Table Insert) در چند جدول SQL Server

در دنیای مدیریت پایگاه داده، اغلب با سناریوهایی روبرو می‌شویم که نیاز به درج داده در بیش از یک جدول به صورت همزمان یا مرتبط وجود دارد. این نیاز می‌تواند برای حفظ یکپارچگی ارجاعی، ثبت تاریخچه تغییرات، یا توزیع داده‌ها به جداول مختلف بر اساس منطق خاص باشد. در SQL Server، روش‌های متعددی برای مدیریت کارآمد عملیات درج چندجدولی (Multiple Table Insert) وجود دارد که هر یک مزایا و کاربردهای خاص خود را دارند. انتخاب روش مناسب بستگی به پیچیدگی عملیات، نیاز به بازگشت‌پذیری (atomicity) و الزامات عملکردی شما دارد.

در ادامه، به بررسی چندین روش کلیدی برای درج داده به چند جدول می‌پردازیم:

۱. استفاده از چندین دستور INSERT مجزا

ساده‌ترین و رایج‌ترین روش، اجرای دستورات INSERT مجزا برای هر جدول است. این روش در شرایطی که ارتباط بین جداول ساده است و نیازی به دریافت کلیدهای تولید شده (مانند IDENTITY) از درج اول برای درج‌های بعدی نیست، کارآمد است. با این حال، در صورت نیاز به بازگشت‌پذیری، باید از تراکنش‌ها (Transactions) استفاده کرد تا اطمینان حاصل شود که تمام عملیات با موفقیت انجام می‌شوند یا در صورت خطا، همگی لغو گردند.

مثال زیر نحوه ایجاد دو جدول و درج داده در هر یک را با دستورات جداگانه نشان می‌دهد:

CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    ProductName NVARCHAR(100)
);

-- استفاده از تراکنش برای اطمینان از یکپارچگی
BEGIN TRANSACTION;

INSERT INTO Customers (CustomerName, Email)
VALUES ('علی احمدی', 'ali.ahmadi@example.com');

-- فرض بر این است که CustomerID دستی وارد یا از منبع دیگری تامین می‌شود
INSERT INTO Orders (CustomerID, OrderDate, ProductName)
VALUES (1, GETDATE(), 'لپ تاپ');

COMMIT TRANSACTION;

در این مثال، دو دستور INSERT در یک تراکنش قرار گرفته‌اند. اگرچه ساده است، اما این روش برای سناریوهایی که نیاز به استفاده از CustomerID تولید شده توسط دستور INSERT در جدول Customers برای درج در جدول Orders داریم، کارآمد نیست و نیاز به رویکردهای پیشرفته‌تری دارد.

۲. استفاده از عبارت OUTPUT برای درج در جداول مرتبط

عبارت OUTPUT یکی از قدرتمندترین ویژگی‌های SQL Server برای درج همزمان داده در جداول مرتبط است. این عبارت به شما امکان می‌دهد تا داده‌های درج شده، به‌روزرسانی شده یا حذف شده را مستقیماً به یک جدول موقت، متغیر جدولی (Table Variable) یا حتی به یک دستور INSERT دیگر هدایت کنید. این قابلیت برای بازیابی کلیدهای اصلی (Primary Keys) تولید شده و استفاده از آن‌ها در درج‌های مرتبط در جداول دیگر بسیار مفید است.

فرض کنید می‌خواهیم یک مشتری جدید را درج کرده و بلافاصله یک سفارش برای آن مشتری جدید ثبت کنیم. با استفاده از OUTPUT می‌توانیم CustomerID تازه تولید شده را دریافت و از آن استفاده کنیم:

DECLARE @NewCustomers TABLE (CustomerID INT, CustomerName NVARCHAR(100));

BEGIN TRANSACTION;

INSERT INTO Customers (CustomerName, Email)
OUTPUT INSERTED.CustomerID, INSERTED.CustomerName INTO @NewCustomers
VALUES ('فاطمه محمدی', 'fatemeh.mohammadi@example.com');

INSERT INTO Orders (CustomerID, OrderDate, ProductName)
SELECT nc.CustomerID, GETDATE(), 'کیبورد مکانیکی'
FROM @NewCustomers nc;

COMMIT TRANSACTION;

در این قطعه کد، CustomerID و CustomerName از ردیف تازه درج شده در جدول Customers به متغیر جدولی @NewCustomers منتقل می‌شوند. سپس این اطلاعات برای درج در جدول Orders استفاده می‌گردد. این روش کارایی بالایی دارد و یکپارچگی داده‌ها را تضمین می‌کند.

۳. استفاده از دستور MERGE

دستور MERGE که برای عملیات “Upsert” (بروزرسانی یا درج) طراحی شده است، می‌تواند در سناریوهای درج داده به چند جدول که منطق پیچیده‌ای برای هماهنگ‌سازی داده‌ها بین منبع و مقصد وجود دارد، به کار گرفته شود. اگرچه MERGE به طور مستقیم برای درج در چند جدول طراحی نشده، اما می‌توان از آن برای درج داده در یک جدول اصلی و سپس با استفاده از عبارات OUTPUT، داده‌های حاصل را به جداول دیگر هدایت کرد. این روش برای سناریوهایی که نیاز به بررسی وجود ردیف قبل از درج یا به‌روزرسانی داریم، ایده‌آل است.

مثال زیر نشان می‌دهد چگونه می‌توان با استفاده از MERGE، مشتری را بر اساس ایمیل درج یا به‌روزرسانی کرد و سپس از OUTPUT برای درج سفارش استفاده کرد (این یک مثال پیشرفته‌تر از کاربرد MERGE در کنار OUTPUT است):

DECLARE @SourceData TABLE (CustomerName NVARCHAR(100), Email NVARCHAR(100), ProductName NVARCHAR(100));
INSERT INTO @SourceData VALUES ('زهرا کریمی', 'zahra.karimi@example.com', 'موس بی‌سیم');

DECLARE @MergedCustomers TABLE (ActionType NVARCHAR(10), CustomerID INT, CustomerName NVARCHAR(100));

MERGE Customers AS Target
USING @SourceData AS Source
ON (Target.Email = Source.Email)
WHEN NOT MATCHED THEN
    INSERT (CustomerName, Email)
    VALUES (Source.CustomerName, Source.Email)
OUTPUT $action, INSERTED.CustomerID, INSERTED.CustomerName INTO @MergedCustomers;

INSERT INTO Orders (CustomerID, OrderDate, ProductName)
SELECT mc.CustomerID, GETDATE(), sd.ProductName
FROM @MergedCustomers mc
JOIN @SourceData sd ON mc.CustomerName = sd.CustomerName
WHERE mc.ActionType = 'INSERT'; -- فقط برای ردیف‌های تازه درج شده سفارش ثبت شود

این مثال از MERGE برای درج مشتری جدید در صورت عدم وجود استفاده می‌کند و سپس CustomerID حاصل از این عملیات را برای درج یک سفارش جدید به کار می‌برد. این رویکرد برای درج شرطی داده و مدیریت همزمان چندین عملیات در یک دستور SQL بسیار قدرتمند است.

۴. استفاده از INSERT…EXEC

دستور INSERT...EXEC به شما این امکان را می‌دهد که خروجی یک رویه ذخیره شده (Stored Procedure) یا یک رشته SQL پویا (Dynamic SQL) را به عنوان ورودی برای یک دستور INSERT استفاده کنید. این روش زمانی مفید است که منطق پیچیده‌ای برای تولید داده‌های ورودی دارید که در یک رویه ذخیره شده کپسوله شده است، یا نیاز به اجرای SQL پویا دارید.

فرض کنید رویه‌ای دارید که لیست مشتریان جدید را تولید می‌کند و می‌خواهید خروجی آن را به جدول Customers و سپس Orders درج کنید:

CREATE PROCEDURE GetNewCustomerData
AS
BEGIN
    SELECT 'سعید حسینی' AS CustomerName, 'saeed.hosseini@example.com' AS Email, 'مانیتور' AS ProductName
    UNION ALL
    SELECT 'مریم قاسمی', 'maryam.ghasemi@example.com', 'هدست'
END;

DECLARE @TempNewCustomers TABLE (CustomerID INT, CustomerName NVARCHAR(100), Email NVARCHAR(100), ProductName NVARCHAR(100));

INSERT INTO Customers (CustomerName, Email)
OUTPUT INSERTED.CustomerID, INSERTED.CustomerName, INSERTED.Email INTO @TempNewCustomers (CustomerID, CustomerName, Email)
EXEC GetNewCustomerData;

-- برای درج سفارشات، نیاز داریم که ProductName را از خروجی اصلی GetNewCustomerData استخراج کنیم
-- این بخش نیازمند بازنگری است زیرا OUTPUT فقط ستون‌های Customers را باز می‌گرداند.
-- برای این مثال، فرض می‌کنیم ProductName را بعداً به TempNewCustomers اضافه کرده‌ایم یا از منبع اصلی قابل دسترسی است.
INSERT INTO Orders (CustomerID, OrderDate, ProductName)
SELECT tnc.CustomerID, GETDATE(), sd.ProductName
FROM @TempNewCustomers tnc
JOIN (SELECT 'سعید حسینی' AS CustomerName, 'مانیتور' AS ProductName
      UNION ALL
      SELECT 'مریم قاسمی', 'هدست') AS sd ON tnc.CustomerName = sd.CustomerName;

نکته مهم در اینجا این است که خروجی EXEC باید با ساختار ستون‌های جدول مقصد مطابقت داشته باشد. برای مدیریت درج‌های پیچیده که شامل چندین مرحله یا منطق شرطی در رویه‌های ذخیره شده است، INSERT...EXEC یک گزینه کارآمد به شمار می‌رود.

۵. استفاده از عبارات جدول مشترک (CTEs)

عبارات جدول مشترک (Common Table Expressions یا CTEs) به خودی خود دستورات درج چندجدولی نیستند، اما می‌توانند به طور چشمگیری خوانایی و قابلیت مدیریت کدهای SQL را در سناریوهای درج داده در جداول متعدد افزایش دهند. CTEs به شما امکان می‌دهند تا مجموعه داده‌های میانی پیچیده‌ای را تعریف کنید که سپس می‌توانند در دستورات INSERT بعدی مورد استفاده قرار گیرند. این روش به ویژه زمانی مفید است که شما نیاز به آماده‌سازی یا تبدیل داده‌ها قبل از درج دارید.

با استفاده از CTE، می‌توانید داده‌های ورودی را یک بار پردازش کنید و سپس نتایج را به چندین دستور INSERT جداگانه ارسال کنید. این کار به بهبود خوانایی کد کمک می‌کند و تضمین می‌کند که منطق آماده‌سازی داده یکپارچه است.

مثال زیر یک CTE را برای انتخاب داده‌ها و سپس استفاده از آن در دو دستور INSERT جداگانه نشان می‌دهد:

WITH NewOrderData AS (
    SELECT
        'رضا نوری' AS CustomerName,
        'reza.nouri@example.com' AS Email,
        'ماوس' AS ProductName
)
BEGIN TRANSACTION;

INSERT INTO Customers (CustomerName, Email)
SELECT CustomerName, Email FROM NewOrderData;

INSERT INTO Orders (CustomerID, OrderDate, ProductName)
SELECT c.CustomerID, GETDATE(), nod.ProductName
FROM Customers c
JOIN NewOrderData nod ON c.Email = nod.Email; -- فرض بر این است که Email منحصر به فرد است
                                                -- یا CustomerID را از INSERT بالا دریافت کرده‌ایم (با OUTPUT بهتر است)

COMMIT TRANSACTION;

در این مثال، NewOrderData یک CTE است که داده‌های مشتری و محصول را تعریف می‌کند. این CTE سپس برای درج در جدول Customers استفاده می‌شود. سپس برای درج در جدول Orders، با جدول Customers جوین می‌شود تا CustomerID مربوطه را پیدا کند. برای سناریوهای واقعی‌تر، ترکیب CTE با عبارت OUTPUT راهکار بهتری برای دریافت CustomerID تولید شده خواهد بود.

نتیجه‌گیری

درج داده به چند جدول در SQL Server را می‌توان با روش‌های مختلفی انجام داد که هر کدام مزایا و پیچیدگی‌های خاص خود را دارند. از ساده‌ترین روش، یعنی استفاده از چندین دستور INSERT مجزا در یک تراکنش، تا رویکردهای پیشرفته‌تری مانند عبارت OUTPUT، دستور MERGE، و INSERT...EXEC، انتخاب بهترین روش بستگی به نیازهای خاص پروژه و ساختار پایگاه داده شما دارد.

برای حفظ یکپارچگی داده‌ها و عملکرد بهینه، به ویژه در محیط‌های با حجم بالای تراکنش، استفاده از OUTPUT در ترکیب با تراکنش‌ها یا CTE ها اغلب کارآمدترین راه حل است. درک صحیح این روش‌ها به شما کمک می‌کند تا کدهای SQL قوی‌تر، قابل نگهداری‌تر و سریع‌تری برای عملیات درج چندجدولی بنویسید.