Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 –  billinkc Aug 30 '13 at 20:23
1  
You might mention which RDBMS you're using. –  Michael - sqlbot Aug 30 '13 at 20:36
    
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? –  a_horse_with_no_name Aug 31 '13 at 20:36
    
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 –  chris.nesbit1 Sep 1 '13 at 0:46

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
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. –  chris.nesbit1 Aug 31 '13 at 19:01
    
@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 ? –  Kin Aug 31 '13 at 19:12
    
I guess my question is... Is there a way to do an insert select that doesn't matter if the table exists yet? –  chris.nesbit1 Sep 1 '13 at 0:41
    
@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. –  Kin Sep 1 '13 at 2:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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