Output Clause

  • Q1. Is it possible to use any other table apart from Inserted in the Output clause in an insert into a table.

    I have a master table with Identity key as primary key and want to use this identity key to insert into another table with additional columns form another table in a bulk insert t-sql

  • I'm something of a newby to the forums, but here's a suggestion:

    I haven't seen a way to join the inserted table in an OUTPUT clause (maybe I'm just missing it), but what if you OUTPUTted your INSERT results to a temp table or table variable and then executed a second insert statement that selects results from the temp table/table variable joined to your additional table?

  • You can use Output clause for that task.

    Actually it is one of the features I love in t-sql

    Please check for examples at T-SQL OUTPUT Clause Sample and T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command

    Please also check the below code

    /*

    CREATE TABLE IDTable1 (

    Id int identity(1,1),

    username varchar(10)

    )

    GO

    CREATE TABLE IDLogTable (

    IDLogTable_Id int identity(1,1),

    IDTable1_Id int

    )

    */

    insert into IDTable1

    output inserted.id into IDLogTable(IDTable1_Id)

    values ('eralper')

    I hope that helps,

  • mpradeesh,

    It would help if you could provide some sample data to illustrate exactly what you want to happen.

    There are a number of alternatives.

    Be aware though that using the OUTPUT clause to directly insert rows to a permanent table has a few restrictions. Specifically, the destination table cannot:

  • Have any enabled triggers
  • Have any CHECK constaints or enabled rules
  • Participate in a FOREIGN KEY relationship (parent or child)
  • Those restrictions often make it impossible to use the OUTPUT clause in this way. If the number of rows is small, consider using a table variable as an intermediate step. Even if those restrictions do not apply, you should know that your code will break if, for example, someone adds a trigger to the table at any point in the future.

    The sample data would help us to give you an accurate solution.

    Paul

  • While the OUTPUT clause does have limitations is it perfect for this type of action. You cannot directly insert into a table with keys as suggested, but you can insert the data into an intermediate local temp table. You can then use this table to drive your joins and insert into the detail table.

    USE [tempdb]

    GO

    CREATE TABLE [Master](id INT IDENTITY PRIMARY KEY CLUSTERED,col CHAR(1))

    CREATE TABLE [Detail](Id INT IDENTITY, MID INT REFERENCES [Master](Id), Col_Descr VARCHAR(50));

    CREATE TABLE [SomeOtherTable](Col CHAR(1), Descr VARCHAR(50))

    INSERT INTO dbo.[SomeOtherTable] VALUES ('a','This is a better description')

    GO

    IF OBJECT_ID('tempdb.dbo.#M') IS NOT NULL

    BEGIN

    DROP TABLE #M;

    END;

    CREATE TABLE #M(ID INT PRIMARY KEY CLUSTERED,Col CHAR(1));

    INSERT INTO dbo.[Master] OUTPUT INSERTED.Id, INSERTED.Col INTO #M

    SELECT 'a'

    INSERT INTO dbo.Detail (MID,Col_Descr)

    SELECT M.ID, sot.Descr

    FROM #M m

    INNER JOIN dbo.[SomeOtherTable] sot ON m.[Col] = sot.Col

    SELECT * FROM dbo.[Master]

    SELECT * FROM dbo.[Detail]

    /*

    id col

    ----------- ----

    1 a

    Id MID Col_Descr

    ----------- ----------- --------------------------------------------------

    1 1 This is a better description

    */

    GO

    --cleanup

    DROP TABLE dbo.Detail

    DROP TABLE dbo.[Master]

    DROP TABLE dbo.[SomeOtherTable]

  • If the restrictions I mentioned don't apply, the following extension of Adam's code illustrates a single-step alternative:

    Setup:

    USE tempdb;

    GO

    CREATE TABLE dbo.MasterRecord

    (

    master_id INTEGER IDENTITY PRIMARY KEY,

    col CHAR(1) NOT NULL,

    );

    CREATE TABLE dbo.DetailRecord

    (

    detail_id INTEGER IDENTITY NOT NULL,

    master_id INTEGER NOT NULL,

    col_desc VARCHAR(50) NOT NULL,

    );

    CREATE TABLE dbo.SomeOtherTable

    (

    col CHAR(1) NOT NULL,

    data VARCHAR(50) NOT NULL,

    );

    INSERT dbo.SomeOtherTable

    (col, data)

    VALUES ('a','This is a better description');

    Single step solution:

    INSERT dbo.DetailRecord

    (master_id, col_desc)

    SELECT INS.master_id, INS.data

    FROM (

    MERGE dbo.MasterRecord MR

    USING (

    SELECT SOT.col, SOT.data

    FROM (VALUES ('a')) New (data)

    JOIN dbo.SomeOtherTable SOT

    ON SOT.col = New.data

    ) MS

    ON (MS.col = MR.col)

    WHEN NOT MATCHED BY TARGET

    THEN INSERT (col) VALUES (MS.col)

    OUTPUT INSERTED.master_id, INSERTED.col, MS.data

    ) INS;

    Results and cleanup:

    SELECT * FROM dbo.MasterRecord

    SELECT * FROM dbo.DetailRecord

    -- Cleanup

    DROP TABLE dbo.DetailRecord;

    DROP TABLE dbo.MasterRecord;

    DROP TABLE dbo.SomeOtherTable;

  • Viewing 6 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply