Load data from text file with variable columns

Total article views: 459 | Views in the last 30 days: 16

 Rate this |   Join the discussion |  Briefcase

This script loads from a formatted text file (E.g. tab delimited) with variable number of columns using BULK INSERT. For instance, the file may have any number of columns and next load (seconds later) the same file may have a different number of columns.

This script reads the first line (column names) extracting the names and using them to create a table dynamically appending the time stamp string to the table name (to ensure uniqueness). After executing the CREATE TABLE directive, and creating a new table, it will execute the second BULK INSERT to load the data to the new table.

The table definition uses varchar(1000) for all the columns to make the load easier. I would suggest to perform data validation the load is complete.

Total article views: 459 | Views in the last 30 days: 16