vendredi 1 avril 2016

how to use output inserted.* into table variable while doing Update

/* I am trying the Output inserted for the first time and not able to understand the Error Message :- Insert Error: Column name or number of supplied values does not match table definition.

Goal: I have a huge claim table with many accounts. I want to update a value to 1 in a few accounts and be able to display the updates.

--SO, I create a @TEMP_tbl with the accounts that I want to update */

DECLARE @TEMP_tbl TABLE ( SORIGCREDITORREFNO VARCHAR(30) ,BCLAIMONHOLD INT )

INSERT INTO @TEMP_tbl (SORIGCREDITORREFNO,BCLAIMONHOLD) VALUES ( '1234',0 )

-- writing my update statement

DECLARE @MYVAR TABLE ( SORIGCREDITORREFNO VARCHAR(30),BCLAIMONHOLD INT)

UPDATE CLAIM SET BCLAIMONHOLD = 1 OUTPUT INSERTED.* INTO @MYVAR WHERE SORIGCREDITORREFNO IN ( SELECT SORIGCREDITORREFNO FROM @TEMP_tbl )

SELECT * FROM @MYVAR
-- want to look at my updates

/* Msg 213, Level 16, State 1, Line 16 Insert Error: Column name or number of supplied values does not match table definition. */

Aucun commentaire:

Enregistrer un commentaire