February 1, 2012 at 8:56 pm
Comments posted to this topic are about the item INSERT OUTPUT
February 1, 2012 at 8:57 pm
February 1, 2012 at 11:24 pm
Nice Question.
-----------------
Gobikannan
February 1, 2012 at 11:41 pm
Good Question !!!.
February 1, 2012 at 11:58 pm
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
February 2, 2012 at 12:27 am
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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 12:58 am
This was removed by the editor as SPAM
February 2, 2012 at 2:13 am
February 2, 2012 at 2:14 am
DATE is not a valid data type... ?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
February 2, 2012 at 2:25 am
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?"
February 2, 2012 at 2:27 am
good one.
February 2, 2012 at 2:28 am
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
----------------------------------------
February 2, 2012 at 3:22 am
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.
February 2, 2012 at 4:30 am
Cheers Steve, I was looking for the trap too!
I love this feature, actually currently writing lots of them for a data migration project.
February 2, 2012 at 4:43 am
Simple question - thanks.
No trap - thanks.
Points today - thanks
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There 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 62 total)
You must be logged in to reply to this topic. Login to reply