在规范化数据库的同时从单个表插入多个表 [英] Inserting From a single Table into multiple tables while Normalizing the database

问题描述

好，所以我有一个带有以下列的表

Ok so i have a table with the following columns

col1        col2       col3
a            a          1
a            a          2
a            b          3
a            b          4
a            b          5

所以我必须分开将上述表分成多个表，同时将col1和col2保留在单独的表中，并将主键与col3的外键关系保留在另一个表中。这就是它的样子。

so i have to split the above mentioned table into multiple tables while keeping col1 and col2 in a separate table and a primary key to foreign key relation with col3 in another table. This is how it looks.

table1

Id        col1         col2


table2
id        col3        table1fk

我能够将表分为两部分表，但它在table1中创建了重复的行，并将它们映射到table2中的单行。
我想实现的是在table1中创建一个不同的行，并将它们映射到table2中的多个不同的行。

I was able to split the table into two tables but it created the duplicate rows in the table1 and mapped them to single row in table2. What i wanted to achieve was create a single distinct row in table1 and map them to multiple distinct rows in table2.

我使用的查询是。

Merge Into table1 As c
Using oldtable ON 1=0

When Not Matched By Target Then
Insert(col1,col2) Values(val1,val2)
Output Inserted.Id,oldtable.val3
Into table2(fktable1,col3);

我可以做些什么来实现它？

What can i do differently to achieve it?

推荐答案

我对 MERGE 不太熟悉，所以我提议使用两个的替代解决方案插入语句：

I'm not really familiar with MERGE so I'm proposing an alternative solution using two INSERT statements:

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO table1(col1, col2)
        SELECT DISTINCT col1, col2 FROM tbl

    INSERT INTO table2(col3, table1fk)
        SELECT
            t.col3,
            t1.Id
        FROM tbl t
        INNER JOIN table1 t1
            ON t1.col1 = t.col1
            AND t1.col2 = t.col2

    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    IF (@@TRANCOUNT > 0) BEGIN
        ROLLBACK TRANSACTION
    END
    DECLARE
            @ErrorNumber    INT,
            @ErrorMessage   NVARCHAR(4000),
            @ErrorState     INT,
            @ErrorSeverity  INT,
            @ErrorLine      INT

    SELECT
        @ErrorNumber    =   ERROR_NUMBER(),
        @ErrorSeverity  =   ERROR_SEVERITY(),
        @ErrorState     =   ERROR_STATE(),
        @ErrorLine      =   ERROR_LINE(),
        @ErrorMessage   =   ERROR_MESSAGE()

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    PRINT 'Error detected, transaction rolled back.'
END CATCH

第一个， INSERT col1，col2 的唯一行放入 table1 。

The first one, INSERTs unique rows of col1,col2 into table1.

第二个，对 tbl 和 table1 JOIN $ c>从 table1 获取FK。

The second one, performs a JOIN on tbl and table1 to get the the FK from table1.

这两个 INSERT 语句只能进行一次事务。

These two INSERT statements must be under one transaction only.

SQL小提琴

这篇关于在规范化数据库的同时从单个表插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！