sql2005 output deleted.* into ?

18 Aug 2006 1:46 PM

WebBuilder451

I'm attempting to use the new tsql capibilitied of 2005 and in the book sql
2005 for developers i saw how the output function seems to work and i have
the following example that does not work. It gives me this message: Incorrect
syntax near 'OUTPUT'. can anyone tell me why?

DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
varchar(150))
delete wrk where tid = 'AD9DB2A8-03E2-4A35-855D-528CC88C08B0'
OUTPUT deleted.* into @queT

wrk matches @queT
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

18 Aug 2006 1:59 PM

Erland Sommarskog

WebBuilder451 (WebBuilder***@discussions.microsoft.com) writes:

> I'm attempting to use the new tsql capibilitied of 2005 and in the book
> sql 2005 for developers i saw how the output function seems to work and
> i have the following example that does not work. It gives me this
> message: Incorrect syntax near 'OUTPUT'. can anyone tell me why?
>
> DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
> varchar(150))
> delete wrk where tid = 'AD9DB2A8-03E2-4A35-855D-528CC88C08B0'
> OUTPUT deleted.* into @queT

Why? You did not read the syntax diagram in Books Online carefully enough,
I guess.

[ WITH <common_table_expression> [ ,...n ] ]
DELETE
    [ TOP ( expression ) [ PERCENT ] ]
    [ FROM ]
    { <object> | rowset_function_limited
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <OUTPUT Clause> ]                    <-----------------------
    [ FROM <table_source> [ ,...n ] ]
    [ WHERE { <search_condition>           <-----------------------

| { [ CURRENT OF

{ { [ GLOBAL ] cursor_name }

| cursor_variable_name

                   }
                ]
              }
            }
    ]
    [ OPTION ( <Query Hint> [ ,...n ] ) ]
[; ]

Yes, I know it may be mean to refer to the manual, but some questions are
more quickly answered by looking up, than waiting for someone to answer them
on a newsgroup. :-)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

18 Aug 2006 2:26 PM

WebBuilder451

thanks for the reply...
I don't see "deleted" any where in there and i'm going by what was in the
book.
As i am trying to retreve the deleted row(s) into a table variable after a
delete this has left me a bit at a loss. Truth is i obviously don't
understand how this works even after looking it up that's why i asked.
true i can do this :
DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
varchar(150))
delete wrk
OUTPUT deleted.* into @queT
but it's not doing what i want as it deletes all rows. are you saying i
can't have a where clause in the delete for this to work? I'd guess not.
which leaves me to think it's in the wrong place? At least i hope...
I do appreciate your response and i hope you can help me out here

like wise i'm having trouble with the insert

insert into wrk (tid, name, other)
values (newid(),'kufdfdt4', 'text')
OUTPUT * inserted.* into @QueT
from wrk

again from the book, whish i admit could be wrong?

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

Show quote

"Erland Sommarskog" wrote:

> WebBuilder451 (WebBuilder***@discussions.microsoft.com) writes:
> > I'm attempting to use the new tsql capibilitied of 2005 and in the book
> > sql 2005 for developers i saw how the output function seems to work and
> > i have the following example that does not work. It gives me this
> > message: Incorrect syntax near 'OUTPUT'. can anyone tell me why?
> >
> > DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
> > varchar(150))
> > delete wrk where tid = 'AD9DB2A8-03E2-4A35-855D-528CC88C08B0'
> > OUTPUT deleted.* into @queT
>
> Why? You did not read the syntax diagram in Books Online carefully enough,
> I guess.
>
> [ WITH <common_table_expression> [ ,...n ] ]
> DELETE
>     [ TOP ( expression ) [ PERCENT ] ]
>     [ FROM ]
>     { <object> | rowset_function_limited
>       [ WITH ( <table_hint_limited> [ ...n ] ) ]
>     }
>     [ <OUTPUT Clause> ]                    <-----------------------
>     [ FROM <table_source> [ ,...n ] ]
>     [ WHERE { <search_condition>           <-----------------------
>             | { [ CURRENT OF
>                    { { [ GLOBAL ] cursor_name }
>                        | cursor_variable_name
>                    }
>                 ]
>               }
>             }
>     ]
>     [ OPTION ( <Query Hint> [ ,...n ] ) ]
> [; ]
>
> Yes, I know it may be mean to refer to the manual, but some questions are
> more quickly answered by looking up, than waiting for someone to answer them
> on a newsgroup. :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

18 Aug 2006 2:29 PM

WebBuilder451

you response did help and i did not see it correctly in the BOL while i was
trying to figure it out the first time.

DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
varchar(150))
delete wrk
OUTPUT deleted.* into @queT
from wrk
where tid = 'B1DA9B6F-A32C-43B3-93A2-A33FB4A65C3C'
select * from @queT

this appeared to be at odds with what i read, but i'm learning

--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes

Show quote

"Erland Sommarskog" wrote:

> WebBuilder451 (WebBuilder***@discussions.microsoft.com) writes:
> > I'm attempting to use the new tsql capibilitied of 2005 and in the book
> > sql 2005 for developers i saw how the output function seems to work and
> > i have the following example that does not work. It gives me this
> > message: Incorrect syntax near 'OUTPUT'. can anyone tell me why?
> >
> > DECLARE @queT TABLE (tid uniqueidentifier, name varchar(50), other
> > varchar(150))
> > delete wrk where tid = 'AD9DB2A8-03E2-4A35-855D-528CC88C08B0'
> > OUTPUT deleted.* into @queT
>
> Why? You did not read the syntax diagram in Books Online carefully enough,
> I guess.
>
> [ WITH <common_table_expression> [ ,...n ] ]
> DELETE
>     [ TOP ( expression ) [ PERCENT ] ]
>     [ FROM ]
>     { <object> | rowset_function_limited
>       [ WITH ( <table_hint_limited> [ ...n ] ) ]
>     }
>     [ <OUTPUT Clause> ]                    <-----------------------
>     [ FROM <table_source> [ ,...n ] ]
>     [ WHERE { <search_condition>           <-----------------------
>             | { [ CURRENT OF
>                    { { [ GLOBAL ] cursor_name }
>                        | cursor_variable_name
>                    }
>                 ]
>               }
>             }
>     ]
>     [ OPTION ( <Query Hint> [ ,...n ] ) ]
> [; ]
>
> Yes, I know it may be mean to refer to the manual, but some questions are
> more quickly answered by looking up, than waiting for someone to answer them
> on a newsgroup. :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>