CODEWITHSUNDEEP
sql-servert-sqlinsert
notJoeKing
notJoeKing

Reputation: 83

Insert from single table into multiple tables, invalid column name error

I am trying to do the following but getting an "Invalid Column Name {column}" error. Can someone please help me see the error of my ways? We recently split a transaction table into 2 tables, one containing the often updated report column names and the other containing the unchanging transactions. This leave me trying to change what was a simple insert into 1 table to a complex insert into 2 tables with unique columns. I attempted to do that like so:

INSERT INTO dbo.ReportColumns
(
    FullName
    ,Type
    ,Classification
)
OUTPUT INSERTED.Date, INSERTED.Amount, INSERTED.Id INTO dbo.Transactions
SELECT
    [Date]
    ,Amount
    ,FullName
    ,Type
    ,Classification
FROM {multiple tables}

The "INSERTED.Date, INSERTED.Amount" are the source of the errors, with or without the "INSERTED." in front.

-----------------UPDATE------------------

Aaron was correct and it was impossible to manage with an insert but I was able to vastly improve the functionality of the insert and add some other business rules with the Merge functionality. My final solution resembles the following:

DECLARE @TransactionsTemp TABLE
(
    [Date] DATE NOT NULL,
    Amount MONEY NOT NULL,
    ReportColumnsId INT NOT NULL
)

MERGE INTO dbo.ReportColumns AS Trgt
USING ( SELECT
            {FK}
    ,[Date]
    ,Amount
    ,FullName
    ,Type
    ,Classification
FROM {multiple tables}) AS Src
ON Src.{FK} = Trgt.{FK} 
WHEN MATCHED THEN
    UPDATE SET 
    Trgt.FullName = Src.FullName,
    Trgt.Type= Src.Type,
    Trgt.Classification = Src.Classification
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
        FullName,
        Type,
        Classification
    )
    VALUES
    (
        Src.FullName,
        Src.Type,
        Src.Classification
    )
OUTPUT Src.[Date], Src.Amount, INSERTED.Id INTO @TransactionsTemp;

MERGE INTO dbo.FinancialReport AS Trgt
USING (SELECT
      [Date] ,
          Amount ,
          ReportColumnsId
          FROM @TransactionsTemp) AS Src
ON Src.[Date] = Trgt.[Date] AND Src.ReportColumnsId = Trgt.ReportColumnsId
WHEN NOT MATCHED BY TARGET And Src.Amount <> 0 THEN
        INSERT
        (
            [Date],
            Amount,
            ReportColumnsId
        )
        VALUES
        (
            Src.[Date],
            Src.Amount,
            Src.ReportColumnsId
        )
WHEN MATCHED And Src.Amount <> 0 THEN
        UPDATE SET Trgt.Amount = Src.Amount
WHEN MATCHED And Src.Amount = 0 THEN
        DELETE;

Hope that helps someone else in the future. :)

Upvotes: 1

Views: 461

Answers (2)

rs.
rs.

Reputation: 27427

Output clause will return values you are inserting into a table, you need multiple inserts, you can try something like following

declare @staging table (datecolumn date, amount decimal(18,2),
                       fullname varchar(50), type varchar(10), 
                       Classification varchar(255));

INSERT INTO @staging
SELECT
    [Date]
    ,Amount
    ,FullName
    ,Type
    ,Classification
FROM {multiple tables}

Declare @temp table (id int, fullname varchar(50), type varchar(10));
INSERT INTO dbo.ReportColumns
(        
    FullName
    ,Type
    ,Classification
)
OUTPUT INSERTED.id, INSERTED.fullname, INSERTED.type INTO @temp
SELECT
    FullName
    ,Type
    ,Classification
FROM @stage

INSERT into dbo.transacrions (id, date, amount)
select t.id, s.datecolumn, s.amount from @temp t
inner join @stage s on t.fullname = s.fullname and t.type = s.type

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280320

I am fairly certain you will need to have two inserts (or create a view and use an instead of insert trigger). You can only use the OUTPUT clause to send variables or actual inserted values ti another table. You can't use it to split up a select into two destination tables during an insert.

If you provide more information (like how the table has been split up and how the rows are related) we can probably provide a more specific answer.

Upvotes: 0

Related Questions