INSERT OUTPUT

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715866

    Comments posted to this topic are about the item INSERT OUTPUT

  • Ron McCullough

    SSC Guru

    Points: 63877

    Going back to the basics - Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Gobikannan

    SSCrazy

    Points: 2735

    Nice Question.

    -----------------
    Gobikannan

  • baabhu

    SSCertifiable

    Points: 6202

    Good Question !!!.

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Great question. I was looking for a trap, but apparently there wasn't any 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Paul White

    SSC Guru

    Points: 150442

    Koen Verbeeck (2/1/2012)


    Great question. I was looking for a trap, but apparently there wasn't any 😀

    Me too. I was expecting:

    GO

    CREATE TABLE dbo.Test

    (

    ID integer IDENTITY PRIMARY KEY,

    AuditDateTime datetime NOT NULL

    );

    GO

    CREATE TRIGGER [trg dbo.Test IOI AuditDateTime]

    ON dbo.Test

    INSTEAD OF INSERT

    AS

    BEGIN

    SET ROWCOUNT 0;

    SET NOCOUNT ON;

    INSERT dbo.Test

    (AuditDateTime)

    SELECT

    GETUTCDATE()

    FROM INSERTED AS ins

    END;

    DECLARE @Output TABLE (ID integer, AuditDateTime datetime);

    INSERT dbo.Test

    OUTPUT INSERTED.*

    INTO @Output

    DEFAULT VALUES;

    -- Zero and NULL!

    SELECT * FROM @Output AS o;

    GO

    DROP TABLE Test;

  • This was removed by the editor as SPAM

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Thanks for a nice and easy question.

    Sometimes it pays to "process" the newsletter from top to bottom, as there was an informative post[/url] by one Steve Jones :laugh:.

    Cheers,

    Michael

  • BenWard

    SSCertifiable

    Points: 5903

    DATE is not a valid data type... ?

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • Cadavre

    SSC-Forever

    Points: 41582

    BenWard (2/2/2012)


    DATE is not a valid data type... ?

    It was introduced for SQL Server 2008, so I guess the question should read "On SQL Server 2008 and above, what is the output of this code?"


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • abhishekgupta109

    SSC Enthusiast

    Points: 109

    good one.

  • BenWard

    SSCertifiable

    Points: 5903

    Ah cool. As expected it worked fine on 2k5 when I set the data type to DATETIME

    Looks like a really useful funtion. I shall be using it a lot I'm sure!

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • cengland0

    SSCertifiable

    Points: 6102

    I like using this function for audit tables. If you have an application that allows users to add, edit, and delete, you can use this method to keep track of the changes performed by those users. Nice if you are asked to provide information such as who updated the record and what was changed.

  • Gazareth

    One Orange Chip

    Points: 27737

    Cheers Steve, I was looking for the trap too!

    I love this feature, actually currently writing lots of them for a data migration project.

  • Stuart Davies

    SSCoach

    Points: 18874

    Simple question - thanks.

    No trap - thanks.

    Points today - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply