CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008relational-database
user1977902
user1977902

Reputation:

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

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

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.

The query i used was.

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?

Upvotes: 1

Views: 1389

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

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

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

These two INSERT statements must be under one transaction only.

SQL Fiddle

Upvotes: 4

Related Questions