You are here
Home > Sql Server > Output option in sql

Output option in sql

OUTPUT OPTION

  • T-SQL supports an OUTPUT clause for modification statements, which we can use to return information from modified rows.
  • We can use OUTPUT clause with update, insert, delete statements.

UPDATE with OUTPUT:

  • With the use of OUTPUT option in update statement we have access to both the old data and new data.
  • In an update statement, inserted prefix represents the state of the rows after the update and deleted represents the state of the rows before the update.
  • Syntax:
  • Update <tablename>

    Set <columnname>=<exp>,

    …….

    < columnname >=<exp>

    OUTPUT

    deleted.columnname as olddata,

    inserted.columnname as newdata

    where <predicate>;

INSERT with OUTPUT:

  • It is used with an insert statement to return information from the inserted rows.
  • We cannot use deleted prefix in an insert statement.
  • To insert rows, we have to use inserted prefix.
  • Syntax:
  • Insert into tablename (column1, column2, ….., columnN)

    OUTPUT

    Inserted. columnname, inserted. columnname, ….

    Values (exp1, exp2, …..);

DELETE with OUTPUT:

  • It is used with delete statement to return information from the deleted rows.
  • Here, we have to use the prefix deleted to refers to the column that are deleted.
  • Syntax:
  • Delete from <tablename>

    OUTPUT

    deleted.columnname, deleted.columnname, …

    where <predicate>;

MERGE with OUTPUT:

  • We can also use OUTPUT option with merge statement.
  • Merge statement can perform different action against the target table. So, when returning output rows, we have to know which action (insert, update or delete) affect the output rows.
  • So, SQL server provide the action function to indicate which action is performed.
  • Syntax:
  •  Merge into <target_tablename> as alias_name

    using <source_tablename> as alias_name[or]

  • using(values(exp1,exp2),(exp1,exp2)) as source_tablename(columns)

    ON <merge_condition>

    when matched then

    <update_statement>

    when not matched by target then

    <insert_statement>

    When not matched by source then

    <delete_statement>

    OUTPUT

    $action as alias_name

    COALESCE (deleted.columnname, inserted.columnname);

Example:

  • Create table
  • Create table sourcetable (id int, fname varchar(50));
  • Insert with output
  •  Insert into sourcetable

    output inserted.*

    values(1,’Gopal’),(2,’Krishna’),(3,’Govind’),(4,’Madhav’);

    output:

    id

    fname
    1

    Gopal

    2

    Krishna

    3

    Govind
    4

    Madhav

                           

      

  • Delete with output
  •  Delete from sourcetable

    output deleted.id, deleted.fname

    where id=4;

    output:

    id

    fname
    4

    Madhav

     

     

  • Update with output
    Update sourcetable

    set fname=’ Kishan ‘

    output inserted.id, deleted.fname as oldname, inserted.fname as newname

    where id=2;

      output:

    id

    oldname newname
    2 Krishna

    Kishan

     

     

  • Merge with output
  • merge into sourcetable as T

    using (Values(1,’diya’),(2,’piya’),(3,’tina’),(4,’sita’)) as tar(id,fname)

    on

    T.id=tar.id

    when matched then

    update set T.fname=tar.fname

    when not matched by target then

    insert (id,fname) values(tar.id, tar.fname)

    output

    $action as action,

    COALESCE(inserted.id, deleted.id) as id;

    output:

    action

    id
    UPDATE

    1

    UPDATE

    2
    UPDATE

    3

    INSERT

    4

     

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top