5

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy).

Is it possible for a delete output into statement to also create a new table in the same way, without having to define the table first?

4

Not that I know of. The OUTPUT.. INTO clause requires an existing table.

On a related note

Using SELECT.. INTO as opposed to INSERT INTO.. SELECT or OUTPUT.. INTO can have negative side-effects depending the situation. Some examples why I think you should create the table first:

  • If you're creating a table inside of a transaction, SQL Server may place a lock on (parts of) the database schema, which would prevent other users from creating, altering or dropping database objects. I'm assuming that this could even affect reading the schema, such as opening a database in Object Explorer in Management Studio. Create the table outside the transaction, then populate inside the transaction.

  • If you want to assign a clustered index to your new table, and that clustered index matches the populating query's sort order, SELECT.. INTO will create the table as a heap (unsorted), so when you apply the clustered index after, SQL Server will have to sort the contents of the table all over again.

2

You can't do it in one line, but here's a quick hack to save having to maintain the create table code, which can come in handy if the table you're deleting from is wide.

Setup

DROP TABLE IF EXISTS #TEST_INPUT
DROP TABLE IF EXISTS #TEST_OUTPUT

CREATE TABLE #TEST_INPUT (ID INT PRIMARY KEY IDENTITY, VAL INT)
INSERT INTO #TEST_INPUT (VAL) VALUES (1),(2),(3)

Create empty output table

This makes an empty copy of the 'input' table with the JOIN removing the identity.

SELECT A.* INTO #TEST_OUTPUT FROM #TEST_INPUT A JOIN #TEST_INPUT B ON 1=0

Delete Into

Now delete into the output table

DELETE FROM #TEST_INPUT OUTPUT DELETED.* INTO #TEST_OUTPUT

(add a where clause to suit)

Yes, it's a bit of a hack - comments appreciated.

  • No need for the JOIN. You could just SELECT * INTO #TEST_OUTPUT FROM #TEST_INPUT WHERE 1=0;. :) – Daniel Hutmacher Feb 8 '16 at 11:52
  • That will preserve the primary key constraint which will cause the delete into to fail (unless identity_insert is on). – Liesel Feb 8 '16 at 11:54
  • 1
    No, the table that you're creating with SELECT .. INTO becomes a heap; entirely without indexes and constraints. – Daniel Hutmacher Feb 8 '16 at 11:55
  • I stand to be corrected but the identity is copied across unless the conditions described in BOL are met. Try the code without the join and it fails with Msg 8101, Level 16, State 1, Line 10 An explicit value for the identity column in table 'TEST_OUTPUT' can only be specified when a column list is used and IDENTITY_INSERT is ON. msdn.microsoft.com/en-AU/library/ms188029.aspx – Liesel Feb 8 '16 at 11:59
  • The DROP ... IF EXISTS is a new functionality, added in 2016 version. I suggest you either remove it or add a note that it's not available in previous versions. – ypercubeᵀᴹ Feb 8 '16 at 12:14
0

Or maybe you could use a trigger, for deleting operation. Here is an example

0

Note that negating the criteria for a SELECT ... INTO will produce the functional equivalent of a DELETE ... INTO. And vice versa.

  • This is an interesting idea. Can you expand with sample code? – ypercubeᵀᴹ Feb 8 '16 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.