SQL Server – Delete into New Table (Like SELECT INTO)

sql servert-sql

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?

Best Answer

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.

Related Solutions

Sql-server – Why can the target of the OUTPUT INTO clause not be a view

From MSDN - Output clause:

"The OUTPUT clause is not supported in the following statements:

DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
INSERT statements that contain an EXECUTE statement.
Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target. "

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Sql-server – Why can the target of the OUTPUT INTO clause not be a view

Sql-server – Combine – Select Into, with Insert Into Select

Related Question