SQL Server中生成多表依赖的插入脚本问题咨询

Hey Manish, I’ve run into this exact dependency issue before when working with legacy SQL scripts pulled from SSMS’s "Generate Scripts" tool—let’s break down how to resolve it and create insert scripts that respect all table relationships.

Step 1: Identify All Dependencies First #

Before writing any insert logic, you need to map out exactly which tables are tied to TABLEA. There are two types of dependencies to check:

• Tables that TABLEA relies on (via foreign keys in TABLEA pointing to other tables)
• Tables that rely on TABLEA (via foreign keys in other tables pointing to TABLEA)

You can use these SQL queries to get the full picture in SSMS:

-- Find tables TABLEA depends on (TABLEA has foreign keys pointing to these)
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ParentTable,
    c.name AS ParentColumn,
    OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
    rc.name AS ReferencedColumn
FROM 
    sys.foreign_keys fk
JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN 
    sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN 
    sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE 
    OBJECT_NAME(fk.parent_object_id) = 'TABLEA';

-- Find tables that depend on TABLEA (these have foreign keys pointing to TABLEA)
SELECT 
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    c.name AS ChildColumn,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
    rc.name AS ParentColumn
FROM 
    sys.foreign_keys fk
JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN 
    sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN 
    sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE 
    OBJECT_NAME(fk.referenced_object_id) = 'TABLEA';

Step 2: Build Insert Scripts in the Right Order #

Dependencies dictate the order of operations. Here’s a typical workflow:

1. Insert into tables TABLEA depends on first: If your TABLEA record references a value that doesn’t exist in a linked table (e.g., TableBId pointing to TABLEB), you need to insert that dependent record first.
2. Insert into TABLEA: Now that all required parent records exist, your TABLEA insert will pass constraint checks.
3. Insert into tables that depend on TABLEA: If you need to add child records tied to the new TABLEA entry, do this last.

Example Script Flow #

Suppose TABLEA has a foreign key to TABLEB, and TABLEC has a foreign key to TABLEA:

-- 1. Insert dependent record into TABLEB (only if it doesn't already exist)
IF NOT EXISTS (SELECT 1 FROM TABLEB WHERE Id = 100)
BEGIN
    INSERT INTO TABLEB (Id, Description) VALUES (100, 'Legacy Dependency Record');
END

-- 2. Insert your target record into TABLEA (pulled from your old script)
INSERT INTO TABLEA (Id, Name, TableBId) VALUES (1, 'New Legacy Record', 100);

-- 3. Insert child record into TABLEC (if needed)
INSERT INTO TABLEC (TableAId, Details) VALUES (1, 'Child record linked to new TABLEA entry');

Step 3: Avoid Duplicate Data Conflicts #

If the legacy script’s record might already exist in your current database, use a safer method to avoid primary key or unique constraint errors:

-- Use MERGE to insert only if the record doesn't exist
MERGE INTO TABLEA AS Target
USING (VALUES (1, 'New Legacy Record', 100)) AS Source (Id, Name, TableBId)
ON Target.Id = Source.Id
WHEN NOT MATCHED THEN
    INSERT (Id, Name, TableBId) VALUES (Source.Id, Source.Name, Source.TableBId);

Step 4: Temporarily Disable Constraints (Use With Caution!) #

If you’re sure the legacy data won’t break referential integrity (and you need a quick fix), you can temporarily disable constraints, insert, then re-enable them. Only do this if you trust the legacy data completely:

-- Disable all foreign key constraints on TABLEA
ALTER TABLE TABLEA NOCHECK CONSTRAINT ALL;

-- Run your insert from the old script
INSERT INTO TABLEA (Id, Name, TableBId) VALUES (1, 'New Legacy Record', 100);

-- Re-enable constraints to enforce integrity going forward
ALTER TABLE TABLEA CHECK CONSTRAINT ALL;

内容的提问来源于stack exchange，提问作者MANISH