Delete with output clause and table aliases

Question

Post reply

Delete with output clause and table aliases

andegre

SSCertifiable

Points: 5875
More actions
June 27, 2011 at 7:26 am

#240479

This is what I'm trying to do:
Delete t
From TableT t
Output Deleted.TableTID,
Deleted.Value1,
Deleted.Value2
Into TableTArchive
(
TableTID,
Value1,
Value2
)
Left Outer Join TableX x On x.TableTID = t.TableTID
Where x.TableXID IS NULL
But it's throwing me an error for TableT's alias...how can I do a table alias name with an output clause?
TIA

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

pure syntax issue...the OUTPUT...INTO goes BEFORE the FROM:

Delete t

Output Deleted.TableTID,

Deleted.Value1,

Deleted.Value2

Into TableTArchive

(

TableTID,

Value1,

Value2

)

From TableT t

Left Outer Join TableX x On x.TableTID = t.TableTID

Where x.TableXID IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andegre SSCertifiable Points: 5875 More actions · Answer 2

That's weird Lowell (thanks by the way). I have an entire archive job that is currently running with the exact same syntax as my original post, but without the table alias...apparently SQL Server doesn't care when there's no table alias.

Thanks again!