Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output Clause Expand / Collapse
Author
Message
Posted Tuesday, July 20, 2010 9:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:11 AM
Points: 10, Visits: 253

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

Post #955691
Posted Tuesday, July 20, 2010 4:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:57 AM
Points: 154, Visits: 170
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?
Post #955978
Posted Tuesday, July 20, 2010 11:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #956111
Posted Wednesday, July 21, 2010 12:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 9,923, Visits: 11,170
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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #956128
    Posted Wednesday, July 21, 2010 2:02 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Thursday, September 11, 2014 1:41 PM
    Points: 2,278, Visits: 3,058
    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
    Post #956731
    Posted Thursday, July 22, 2010 2:18 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Today @ 10:07 AM
    Points: 9,923, Visits: 11,170
    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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #956989
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse