SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output Clause


Output Clause

Author
Message
mpradeesh
mpradeesh
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 348
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
pbarnthson
pbarnthson
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 220
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?
Eralper
Eralper
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 466
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,

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Paul White
Paul White
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20530 Visits: 11359
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



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Adam Haines
    Adam Haines
    Hall of Fame
    Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

    Group: General Forum Members
    Points: 3892 Visits: 3135
    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]





    My blog: http://jahaines.blogspot.com
    Paul White
    Paul White
    SSC-Insane
    SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

    Group: General Forum Members
    Points: 20530 Visits: 11359
    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;





    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search