2

I'm trying to insert daily imported data into a SQL Server (2017) table. While most of the time the imported data has a fixed amount of columns, sometimes the client wants to add a new column to the data-to-be-imported.

I'm seeking for a solution that when the data gets imported (whether it is from another table, from R or from .csv's, don't mind this), SQL would automatically add the missing (extra) column to the parent table, providing the column name and assigning NULL to all previous entries.

I've tried with both UNION ALL and BULK INSERT, but both of these require the same # of columns. I'm working with SSMS2017, R3.4.1.

Next, I tried with a staging table and modifying the UNION clause as:

SELECT * FROM Table_new
UNION ALL
SELECT Tp.*, '' FROM Table_parent Tp;

But more often than not the extra column doesn't occur, so the column dimension problem occurs again.

I also thought about running the queries from R with DBI and odbc dbWriteTable() and handling the invalid column error with TryCatch(), parsing the column name from the error message and so on, but this would be a shakiest craft I've ever done and would prefer not to.

Ultimately I thought adding an if clause in R, and depending on the number of added new columns, loop and add the ', ""' part to the SQL query to create the extra columns. I'm convinced that this is too complex solution to this problem. 

# Pseudo-R

#calculate the difference between lenght(colnames)
diff <- diff(length(colnames_new, colnames_parent)

if diff = 0 {
    dbQuery(BULK INSERT INTO old SELECT * FROM new;)
} else if diff > 0 {
    dbQuery(paste0(SELECT * FROM new
    UNION ALL
    SELECT T1.*, loop_paste(, '' /* for every diff */), FROM parent T1;))
} else if diff < 0 {
    dbQuery(SELECT * FROM parent
    UNION ALL
    SELECT T2.*, loop_paste(, '' /* for every diff */), FROM new T2;))
}

To summarize: when inserting data to SQL table, how to (automatically) append the columns in the parent table, when necessary? Thanks!

topiaa
  • 21
  • 3
  • Short answer - you can't. Your code will need to evaluate the structure of the data in your file, determine if it has "new" columns, create those columns in the destination table, and then import them. How you do this exactly is up to you. You worry about "shakiest craft" but IMO this goal is already shaky. There is no shortcut - no insert (or similar) statement will automatically add a column to an existing table. – SMor May 16 '19 at 12:40

1 Answers1

0

The things in your database such as tables, columns, primary keys, foreign keys, check clauses are all part of the database schema. People design the schema before adding data to the database.

If you want to add new columns then you have to redesign your schema. When you do this you will also have to rewrite some of the CRUD procedures.

Ken Evans
  • 362
  • 1
  • 11