Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...

If I were to do this...

INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable

I'd get the ID of the last inserted record into Table1.

Is a CURSOR or WHILE loop the only ways to do this?

share|improve this question
    
Surely you can join to Table1 in your query to populate Table2? –  Colin 't Hart Aug 11 at 11:31
    
But if I wanted the PK of Table1 to be used in Table2 as a foreign key that wouldn't work –  m4rc Aug 11 at 12:10
1  
Isn't there some way to identify A,B,C belonging to the same D,E,F from the original source? What is the primary key of MyTable (or DataTable, you changed your mind mid-question)? –  Aaron Bertrand Aug 11 at 12:46
    
I think that's what I'll have to do. It's good to get a fresh set of eyes on it. Sorry if it looked like I changed my mind mid question but the idea always ways that if there was a row in one there would be a foreign key in the other. Apologies for my poor question asking skills –  m4rc Aug 11 at 13:13

4 Answers 4

up vote 2 down vote accepted

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves.

Anyway, the solution would look something like this:

MERGE INTO Table1 AS t1
USING MyTable ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (A, B, C) VALUES (t1.A, t1.B, t1.C)

--- .. and INSERT into Table2:
OUTPUT inserted.ID, MyTable.D, MyTable.E, MyTable.F
INTO Table2 (ID, D, E, F);

MERGE, as opposed to the other DML statements, can reference other tables than just inserted and deleted, which is useful for you here.

More: http://sqlsunday.com/2013/08/04/cool-merge-features/

share|improve this answer

If this is something you are planning to do regularly (i.e. it is part of the application logic and not a one-off data transformation exercise) then you could use a view onto Table1 and Table2 with an INSTEAD OF INSERT trigger to manage splitting the data (and arranging the keys/relationships) - then you would just do:

INSERT newView SELECT NEWID(), A, B, C, D, E, F FROM MyTable

and the trigger could be as simple as:

CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS 
    INSERT table1 SELECT ID, A, B, C FROM inserted
    INSERT table2 SELECT ID, D, E, F FROM inserted
GO

assuming the view is something like:

CREATE VIEW newView AS 
SELECT table1.ID, A, B, C, D, E, F 
FROM table1 
    JOIN table2 ON table1.ID = table2.ID;

or if there might be rows in each table without matching rows in the other:

CREATE VIEW newView AS 
SELECT ISNULL(table1.ID, table2.ID), A, B, C, D, E, F 
FROM table1 
    FULL OUTER JOIN table2 ON table1.ID = table2.ID;

(of course what rows are output when you SELECT from the view is unimportant if you don't intend to SELECT from it and it only exists to provide a template to INSERT into for the trigger to do its magic)

This is assuming that you are intending to use a UUID type for your primary key in this case - if you are using an automatically incrementing integer key on table1 there is a little more work to do. Something like the following might work:

CREATE trg_newview_insert TRIGGER newView INSTEAD OF UPDATE AS 
    INSERT table1 (A, B, C) 
    SELECT A, B, C 
    FROM inserted;
    INSERT table2 (ID, D, E, F) 
    SELECT ID, D, E, F 
    FROM table1 AS t 
        JOIN inserted AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C;
GO

and in fact that pair of INSERT statements might work directly as a one-off like so (whether you are using an INT IDENTITY or UNIQUEIDENTIFIER DEFAULT NEWID() type for the key):

INSERT table1 (A, B, C) 
SELECT A, B, C 
FROM MyTable;
INSERT table2 (ID, D, E, F) 
SELECT ID, D, E, F 
FROM table1 AS t 
    JOIN MyTable AS i ON t.A = i.A AND t.B = i.B AND t.C = i.C;

negating the need for the view and trigger completely, though if this is an operation you will be performing often in your code the view+trigger would still be worth considering to abstract out the need for multiple statements each time.

CAVEAT: all the above SQL has been typed from thought and not tested, it will need work before there is any guarantee it will work as you need.

share|improve this answer

Seems like you want:

INSERT dbo.Table1(A,B,C) SELECT A,B,C 
  FROM dbo.DataTable WHERE <identify one row>;

INSERT dbo.Table2(ID,D,E,F) SELECT SCOPE_IDENTITY(),D,E,F
  FROM dbo.DataTable WHERE <identify that same row>;

Or maybe just use one table, if you're always going to have a row in each table... do you have a good reason for splitting these up into multiple tables?

share|improve this answer
1  
The system was in place before I worked on the project and the SE in charge wanted to try out table inheritance, which is fine if you're using Entity Framework and doing stuff from code because it hides everything from you but when you have to switch to ADO because of the poor performance, it's a nightmare! –  m4rc Aug 11 at 13:27

From reading your question, and the comments on the other answers, it seems like you are attempting to fix a problem with DataTable by splitting it into two new tables.

I assume DataTable does not already have a single unique-field such as an IDENTITY(1,1)? If not, perhaps you should add one that you could use for inserting data into Table1 and Table2.

By way of an example; I've created a sample schema, inserted test data into DataTable, modified DataTable to have an IDENTITY(1,1) column, then used that to insert data into both Table1 and Table2:

USE tempdb;
GO

CREATE TABLE dbo.DataTable
(
    A INT
    , B INT
    , C INT
    , D INT
    , E INT
    , F INT
);

INSERT INTO dbo.DataTable (A, B, C, D, E, F)
VALUES (1, 2, 3, 11, 12, 13)
    , (4, 5, 6, 14, 15, 16)
    , (7, 8, 9, 17, 18, 19);

CREATE TABLE dbo.Table1
(
    Table1PK INT NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , A INT
    , B INT
    , C INT
);

CREATE TABLE dbo.Table2
(
    Table2PK INT NOT NULL CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Table1PK INT NOT NULL CONSTRAINT FK_Table2_Table1_PK FOREIGN KEY REFERENCES dbo.Table1(Table1PK)
    , D INT
    , E INT
    , F INT
);

ALTER TABLE dbo.DataTable ADD TempCol INT NOT NULL IDENTITY(1,1);

SET IDENTITY_INSERT dbo.Table1 ON;

INSERT INTO Table1 (Table1PK, A, B, C)
SELECT TempCol, A, B, C 
FROM DataTable;

SET IDENTITY_INSERT dbo.Table1 OFF;

INSERT INTO Table2 
SELECT Table1PK, D, E, F 
FROM dbo.DataTable DT
    INNER JOIN dbo.Table1 T ON DT.TempCol = T.Table1PK;

SELECT *
FROM dbo.Table1;

SELECT *
FROM dbo.Table2;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.