راهنمای جامع ساخت اسکریپت INSERT در SQL Server انتقال داده ها

ساخت ساخت اسکریپت INSERT در SQL Server: راهنمای جامع انتقال داده‌ها

انتقال و اسکریپت‌نویسی داده‌ها از یک محیط به محیط دیگر، به ویژه از سرورهای Production به محیط‌های توسعه (Development) یا تست (Test)، یکی از چالش‌های رایج مدیران پایگاه داده و توسعه‌دهندگان است. هدف از این کار معمولاً بازسازی سناریوهای خاص، عیب‌یابی مشکلات یا تست عملکرد سیستم با داده‌های واقعی است. در این مقاله به بررسی روش‌های کارآمد برای تولید اسکریپت‌ INSERT در SQL Server می‌پردازیم که به شما امکان می‌دهد داده‌ها را به صورت ایمن و دقیق بین پایگاه‌های داده منتقل کنید.

رویکرد اصلی ما برای تولید اسکریپت‌های INSERT، استفاده از یک کوئری SELECT پویا است. این کوئری به شما اجازه می‌دهد تا مقادیر موجود در جدول منبع را انتخاب کرده و آن‌ها را به عنوان بخشی از دستورات INSERT در جدول مقصد قالب‌بندی کنید. این روش انعطاف‌پذیری بالایی دارد و می‌تواند برای انواع داده‌ها و سناریوهای مختلف سفارشی‌سازی شود.

اسکریپت‌نویسی INSERT برای یک جدول

برای شروع، رایج‌ترین سناریو را در نظر می‌گیریم: ساخت اسکریپت INSERT برای تمام ستون‌های یک جدول خاص. این اسکریپت از کاتالوگ سیستمی SQL Server برای شناسایی نام ستون‌ها و انواع داده‌های آن‌ها استفاده می‌کند تا یک رشته INSERT دقیق تولید کند.

در ابتدا، نیاز داریم که نام پایگاه داده و نام جدول مورد نظر خود را مشخص کنیم. این اطلاعات برای تولید اسکریپت صحیح ضروری هستند. کوئری زیر، یک دستور INSERT برای هر سطر در جدول انتخاب‌شده تولید می‌کند، با حذف ستون‌های Identity:

USE YourDatabaseName;
GO

DECLARE @TableName NVARCHAR(128) = N'YourTableName';
DECLARE @SchemaName NVARCHAR(128) = N'dbo'; -- نام شمای جدول را در صورت نیاز تنظیم کنید
DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL = @SQL + 
    'INSERT INTO ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + '(' +
    STUFF((
        SELECT ',' + QUOTENAME(c.COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME 
        AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(t.TABLE_SCHEMA)+'.'+QUOTENAME(t.TABLE_NAME)),c.COLUMN_NAME,'IsIdentity') = 0 
        ORDER BY c.ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '') + ') SELECT ' + 
    STUFF((
        SELECT ',' + 
            CASE 
                WHEN c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') THEN 'ISNULL('''''''' + REPLACE([' + c.COLUMN_NAME + '],'''''''','''''''''''') + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('datetime', 'smalldatetime', 'date', 'time', 'datetime2', 'datetimeoffset') THEN 'ISNULL('''''''' + CONVERT(NVARCHAR(MAX),[' + c.COLUMN_NAME + '],121) + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('uniqueidentifier') THEN 'ISNULL('''''''' + CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX)) + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('image', 'varbinary', 'binary', 'geography', 'geometry') THEN 'ISNULL(''0x'' + CAST([' + c.COLUMN_NAME + '] AS VARBINARY(MAX)),''NULL'')'
                ELSE 'ISNULL(CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX)),''NULL'')' 
            END
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME 
        AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(t.TABLE_SCHEMA)+'.'+QUOTENAME(t.TABLE_NAME)),c.COLUMN_NAME,'IsIdentity') = 0 
        ORDER BY c.ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '') +
    ' FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES t 
WHERE t.TABLE_TYPE = 'BASE TABLE' 
  AND t.TABLE_NAME = @TableName 
  AND t.TABLE_SCHEMA = @SchemaName;

PRINT @SQL;

توضیح: این اسکریپت یک دستور SELECT پویا را برای استخراج و قالب‌بندی داده‌ها از جدول منبع (YourTableName) تولید می‌کند. مقادیر برای انواع داده‌های مختلف مانند CHAR، VARCHAR، NVARCHAR، DATETIME، UNIQUEIDENTIFIER، و انواع باینری و فضایی (IMAGE، VARBINARY، GEOGRAPHY، GEOMETRY) به درستی فرمت می‌شوند. ستون‌های Identity به طور خودکار از اسکریپت‌نویسی حذف می‌شوند تا از بروز خطا جلوگیری شود و مقادیر NULL نیز به درستی هندل می‌شوند.

استخراج داده‌ها از یک جدول با ستون‌های Identity

ستون‌های Identity، که به طور خودکار مقادیر را تولید می‌کنند، نیازمند برخورد ویژه‌ای هنگام اسکریپت‌نویسی INSERT هستند. برای درج صریح مقادیر در یک ستون Identity، باید ویژگی IDENTITY_INSERT را برای جدول فعال کنید. این کار به SQL Server اجازه می‌دهد که مقادیر مشخص شده را به جای تولید خودکار، در ستون Identity درج کند.

برای تولید خودکار اسکریپت INSERT که ستون‌های Identity را نیز شامل می‌شود، باید قبل از دستورات INSERT، SET IDENTITY_INSERT YourTableName ON; و پس از آن، SET IDENTITY_INSERT YourTableName OFF; را اضافه کنید. این روش، زمانی که شما نیاز به حفظ مقادیر کلید اصلی (Primary Key) از سیستم منبع دارید، بسیار حیاتی است.

USE YourDatabaseName;
GO

DECLARE @TableName NVARCHAR(128) = N'YourTableName';
DECLARE @SchemaName NVARCHAR(128) = N'dbo'; -- نام شمای جدول را در صورت نیاز تنظیم کنید
DECLARE @SQL NVARCHAR(MAX) = N'';
DECLARE @IdentityColumnExists BIT = 0;

-- بررسی وجود ستون Identity در جدول
IF EXISTS (
    SELECT 1
    FROM sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = @TableName AND s.name = @SchemaName AND c.is_identity = 1
)
BEGIN
    SET @IdentityColumnExists = 1;
    SET @SQL = 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ON;' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END

SELECT @SQL = @SQL + 
    'INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' +
    STUFF((
        SELECT ',' + QUOTENAME(c.COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_SCHEMA = @SchemaName AND c.TABLE_NAME = @TableName 
        ORDER BY c.ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '') + ') SELECT ' + 
    STUFF((
        SELECT ',' + 
            CASE 
                WHEN c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') THEN 'ISNULL('''''''' + REPLACE([' + c.COLUMN_NAME + '],'''''''','''''''''''') + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('datetime', 'smalldatetime', 'date', 'time', 'datetime2', 'datetimeoffset') THEN 'ISNULL('''''''' + CONVERT(NVARCHAR(MAX),[' + c.COLUMN_NAME + '],121) + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('uniqueidentifier') THEN 'ISNULL('''''''' + CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX)) + ''''''''',''NULL'')'
                WHEN c.DATA_TYPE IN ('image', 'varbinary', 'binary', 'geography', 'geometry') THEN 'ISNULL(''0x'' + CAST([' + c.COLUMN_NAME + '] AS VARBINARY(MAX)),''NULL'')'
                ELSE 'ISNULL(CAST([' + c.COLUMN_NAME + '] AS NVARCHAR(MAX)),''NULL'')' 
            END
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_SCHEMA = @SchemaName AND c.TABLE_NAME = @TableName 
        ORDER BY c.ORDINAL_POSITION
        FOR XML PATH('')
    ), 1, 1, '') +
    ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES t 
WHERE t.TABLE_TYPE = 'BASE TABLE' 
  AND t.TABLE_NAME = @TableName 
  AND t.TABLE_SCHEMA = @SchemaName;

IF @IdentityColumnExists = 1
BEGIN
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' OFF;' + CHAR(13) + CHAR(10);
END

PRINT @SQL;
```

توضیح: این اسکریپت نیز همانند اسکریپت قبلی، یک دستور SELECT پویا را برای تولید مقادیر INSERT می‌سازد، با این تفاوت که تمام ستون‌ها، از جمله ستون Identity، را در نظر می‌گیرد. بخش اصلی شامل فعال‌سازی IDENTITY_INSERT قبل از دستورات درج و غیرفعال کردن آن پس از اتمام است، که برای حفظ مقادیر اصلی Identity ضروری است.

اسکریپت‌نویسی INSERT برای چندین جدول با وابستگی

در پایگاه‌های داده‌ای که دارای روابط پیچیده بین جداول (مانند کلیدهای خارجی) هستند، ترتیب درج داده‌ها بسیار مهم است. اگر بخواهید داده‌ها را برای چندین جدول که به هم وابسته هستند اسکریپت کنید، باید ابتدا جداولی را که دارای کلید خارجی به جداول دیگر نیستند (یا جداول والد) درج کنید، سپس جداولی را که به این جداول والد وابسته هستند، درج نمایید.

یکی از رویکردهای کارآمد برای تعیین ترتیب صحیح درج، استفاده از یک حلقه (Loop) و بررسی وابستگی‌ها از طریق کاتالوگ سیستمی است. با استفاده از sys.foreign_keys می‌توانید روابط بین جداول را شناسایی کرده و یک گراف وابستگی بسازید تا ترتیب درج مشخص شود. این کار به جلوگیری از خطاهای نقض محدودیت کلید خارجی کمک می‌کند.

اسکریپت زیر یک مثال مفهومی برای شناسایی جداول بر اساس وابستگی‌های کلید خارجی آن‌ها ارائه می‌دهد. برای تولید اسکریپت کامل INSERT برای چندین جدول، نیاز به یک راه‌حل داینامیک و تکراری (مثلاً با استفاده از Recursive CTE یا Cursor) است که جداول را بر اساس وابستگی‌هایشان مرتب کرده و برای هر یک، اسکریپت INSERT جداگانه تولید کند.

-- این یک مثال مفهومی برای مرتب‌سازی جداول بر اساس وابستگی‌های خارجی است
SELECT 
    t.name AS TableName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    CASE 
        WHEN EXISTS (SELECT 1 FROM sys.foreign_keys fk WHERE fk.parent_object_id = t.object_id) THEN 1 -- دارای کلید خارجی خروجی است (جدول فرزند)
        ELSE 0 -- کلید خارجی خروجی ندارد یا به جدول دیگری وابسته نیست (جدول والد)
    END AS HasOutgoingForeignKey
FROM sys.tables t
WHERE t.is_ms_shipped = 0 -- جداول سیستمی را حذف کنید
ORDER BY HasOutgoingForeignKey ASC, t.name;

-- برای یک راه‌حل جامع، می‌توانید یک CTE بازگشتی برای ساخت گراف وابستگی و تولید اسکریپت‌ها به ترتیب صحیح استفاده کنید.

ابزارهایی مانند SQL Data Compare از Redgate می‌توانند به صورت خودکار این کار را انجام دهند و فرآیند اسکریپت‌نویسی برای جداول وابسته را ساده‌تر کنند.

هندل کردن داده‌های باینری و فضایی

داده‌های باینری مانند VARBINARY(MAX) و IMAGE، و همچنین داده‌های فضایی GEOMETRY و GEOGRAPHY، نیاز به تبدیل خاصی به فرمت رشته‌ای برای درج دارند. SQL Server برای این منظور از پیشوند 0x برای مقادیر هگز دسیمال استفاده می‌کند.

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

-- مثالی از تبدیل داده باینری به رشته هگزادسیمال برای درج
DECLARE @BinaryData VARBINARY(MAX) = 0x0123456789ABCDEF;
SELECT 'INSERT INTO MyTable (BinaryColumn) VALUES (0x' + 
       CAST('' AS XML).value('xs:hexBinary(sql:variable("@BinaryData"))', 'NVARCHAR(MAX)') + ');';
GO

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

• پشتیبان‌گیری منظم: همیشه قبل از انجام عملیات گسترده درج یا تغییر داده، از پایگاه داده خود پشتیبان‌گیری کنید.
• تست در محیط‌های غیرتولید: اسکریپت‌های تولید شده را همیشه ابتدا در محیط‌های توسعه یا تست اجرا کنید تا از صحت عملکرد آن‌ها اطمینان حاصل کنید.
• مدیریت تراکنش‌ها: برای عملیات درج انبوه، استفاده از تراکنش‌ها (BEGIN TRAN، COMMIT TRAN، ROLLBACK TRAN) می‌تواند به حفظ یکپارچگی داده‌ها در صورت بروز خطا کمک کند.
• عملکرد: برای جداول بسیار بزرگ، روش تولید اسکریپت سطر به سطر ممکن است زمان‌بر باشد. در این موارد، استفاده از ابزارهای ETL مانند SQL Server Integration Services (SSIS) یا دستور BULK INSERT می‌تواند گزینه‌های سریع‌تری باشند.

نتیجه گیری

تولید اسکریپت‌های INSERT در SQL Server یک مهارت ضروری برای هر متخصص پایگاه داده است. با استفاده از تکنیک‌های T-SQL پویا، می‌توانید اسکریپت‌هایی ایجاد کنید که مقادیر را برای انواع داده‌ها از جمله Identity، باینری و فضایی، به درستی قالب‌بندی و درج کنند. این روش‌ها به شما امکان می‌دهند تا داده‌ها را به طور مؤثر و با حداقل خطا بین محیط‌های مختلف SQL Server منتقل کنید و بهینه‌سازی‌های لازم را در فرآیندهای توسعه و تست خود به کار گیرید. به یاد داشته باشید که همیشه بهترین شیوه‌ها را رعایت کنید و اسکریپت‌های خود را قبل از اجرا در محیط‌های حیاتی به دقت آزمایش کنید.