Combine - Select Into, with Insert Into Select

Question

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables.

I'm automating a coworker's sql scripts. Currently these sql scripts create new tables (assuming they don't exist) using Select Into, and this is causing a dilemma. 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'd prefer not to tell my coworker to rewrite his hundreds of lines of code by specifying all the column names twice in all his scripts. Is there some minimalist way I can combine the idea of the "Select Into" and the "Insert Into" into a single query, without explicitly duplicating all the column names? maybe like "Select Into ... On Error " or something like that?

EDIT: I'm using MS SQL

• billinkc: You might be able to do something janky like test for the existence of the table. If it exists, rename it to a temporary and unique name. Coworkers "dumb" code fires always creating a new table and then you dump the temp table's content into the new table. Ugly, hackish but the core logic can remain untouched
• Michael - sqlbot: You might mention which RDBMS you're using.
• user1822: Sounds like a strange design where you constantly need to create new tables. Tables are usually created once and then used by the application. What problem are you trying to solve by constantly creating new tables?
• chris.nesbit1: These scripts were initially written for an environment where the tables could just be overwritten (simple, done and done). But i was given the task of reusing the scripts in a multitenant database where the tables need to persist. And I want to know the simplest/best way to do it

Answer 1

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

• chris.nesbit1: I like the idea of select into where 1=0, wrapped ina try catch. The thing is, these SQL script "massage" data with a lot of calculations. So my coworker will just have to specify dummy data of the desired data type in his "where 1=0" queries. It would be nice if there was a way to incorporate the insert to new and existing tables into a single query and only have to specify the columns once: that would be the most ideal, if there is a way to accomplish it that isn't overly convoluted.
• Kin Shah: @chris.nesbit1 You have to use the if exists..else condition to check the existence of current table. I dont quiet understand what you mean by if there was a way to incorporate the insert to new and existing tables into a single query and only have to specify the columns once: that would be the most ideal? How are you going to differentiate between existing and new table ?
• chris.nesbit1: I guess my question is... Is there a way to do an insert select that doesn't matter if the table exists yet?
• Kin Shah: @chris.nesbit1 Is there a way to do an insert select that doesn't matter if the table exists yet? Not to my knowledge. The approach that I mentioned is the only way that I know of.