locked
Can this be done possibly? RRS feed

  • Question

  • Hello,

    declare @iTable
                table(
                      id int,
                      descA,

                      descB

    )

    Update [myTable] set
                [A] = 'B'
                OUTPUT blah.id, deleted.A, NULL into @iTable

     from blahblah..

    So let's say @iTable have following data..

    id / descA/ descB

    1 / Blah /NULL

    I need another output from different table..so like this..

    Update [mySecondTable] Set

                Blahblah = 'BlahSecond'

                OUTPUT inserted... // here..I want to update @iTable based on the id, which is 1...

                                // so...this ouput will update the @iTable based on the id key...

    so the final table data looks like this...

    id / descA/ descB

    1 /Blah/ BlahSecond.

    Can this be done possibly?

    Thanks,

    Wednesday, May 27, 2015 2:00 PM

Answers

  • Hi T J,

    The data returned from a OUTPUT clause can be only inserted into a table, to achieve your requirement, update another table, you may reference the below workaround. See more details about OUTPUT by clicking here.

    declare @iTable
                table(
                      id int,
                      descA CHAR(10),
                      descB CHAR(10)
    )
    
    select * from @itable
    
    --create table mytable(ID INT,A CHAR(10))
    --INSERT INTO mytable VALUES(1,'Blah ')
     
    
    Update [myTable] set
                [A] = 'B'
                OUTPUT DELETED.id, deleted.A, NULL into @iTable
    
    SELECT * FROM @iTable
    
    --CREATE TABLE MySecondTable(ID INT,B CHAR(10))
    --INSERT INTO MySecondTable VALUES(1,'Blah')
    
    declare @iTable2
                table(
                      id int,
                      descA CHAR(10),
                      descB CHAR(10)
    )
    Update [MySecondTable] set [B] = 'BlahSecond'
    		OUTPUT inserted.id,NULL,inserted.B INTO @iTable2;
    
    MERGE @iTable AS TAR 
    	USING @iTable2 AS SRC
    	ON TAR.ID = SRC.ID
    	WHEN MATCHED THEN
    	UPDATE SET descB = SRC.descB;
    
    SELECT * FROM @iTable
    
    	  

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support



    • Edited by Eric__Zhang Thursday, May 28, 2015 3:07 AM
    • Proposed as answer by Eric__Zhang Monday, June 8, 2015 2:27 AM
    • Marked as answer by Eric__Zhang Tuesday, June 9, 2015 7:47 AM
    Thursday, May 28, 2015 3:06 AM

All replies

  • You could do it with triggers or take a look at Change Data Capture.

    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, May 27, 2015 2:42 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

    The use of OUTPUT is pure dialect. My best guess, based on nothing you posted, is that this ought be put into a Stored Procedure  that holds a transaction to assure both tables are in synch. Then we probably need DRI  actions to enforce this business rule.  

    Oh, we use /* ..*/ or -- for comments, not // from whatever language your mind set is still locked into. It probably has no transaction concpet. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, May 27, 2015 4:02 PM
  • Hi T J,

    The data returned from a OUTPUT clause can be only inserted into a table, to achieve your requirement, update another table, you may reference the below workaround. See more details about OUTPUT by clicking here.

    declare @iTable
                table(
                      id int,
                      descA CHAR(10),
                      descB CHAR(10)
    )
    
    select * from @itable
    
    --create table mytable(ID INT,A CHAR(10))
    --INSERT INTO mytable VALUES(1,'Blah ')
     
    
    Update [myTable] set
                [A] = 'B'
                OUTPUT DELETED.id, deleted.A, NULL into @iTable
    
    SELECT * FROM @iTable
    
    --CREATE TABLE MySecondTable(ID INT,B CHAR(10))
    --INSERT INTO MySecondTable VALUES(1,'Blah')
    
    declare @iTable2
                table(
                      id int,
                      descA CHAR(10),
                      descB CHAR(10)
    )
    Update [MySecondTable] set [B] = 'BlahSecond'
    		OUTPUT inserted.id,NULL,inserted.B INTO @iTable2;
    
    MERGE @iTable AS TAR 
    	USING @iTable2 AS SRC
    	ON TAR.ID = SRC.ID
    	WHEN MATCHED THEN
    	UPDATE SET descB = SRC.descB;
    
    SELECT * FROM @iTable
    
    	  

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support



    • Edited by Eric__Zhang Thursday, May 28, 2015 3:07 AM
    • Proposed as answer by Eric__Zhang Monday, June 8, 2015 2:27 AM
    • Marked as answer by Eric__Zhang Tuesday, June 9, 2015 7:47 AM
    Thursday, May 28, 2015 3:06 AM