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
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