Press "Enter" to skip to content

OUTPUT With Identity Columns

Kenneth Fisher walks through a scenario trying to archive data using the OUTPUT clause, but where there is an identity column involved:

So what’s the problem? Well, to start with by using the SELECT TOP 0 INTO Archive method of creating the Archive table you’ve created the archive table with an identity column. Get rid of that identity property (you still want the column) and you don’t have a problem. But the really cool part of this is that it reminded me that the OUTPUT clause has a field list.

Also check out the comments for another solution.