At one point in my career I was asked to export a table that was over 2,000 columns long from one of our applications and read it onto a SQL server. Through trial and error I learned several important rode blocks to doing this. The first was that there is a 1,024 column limit for tables from SQL engine. Another was that having so much data was causing SSMS to freeze up. And, having over 100,000 rows, the SSMS import wizard was miss identifying the data types each column should be. It was a difficult decision, but I spilt the table into two in order to overcome the column limit. But overcoming the other two required quit a bit more ingenuity.
I ended up creating a VBA tool in Excel to put together SQL statements. The program would first read the export and determine the data type of each column. Then it would write a SQL statement to create the table. And finally it would create insert statements grouped by every 50,000 so that I could read in the data in batches without trouble. I’ve also included a second macro that creates insert or update files. This one isn’t attached to a button, but you can pull it up by pressing ALT+F11.
Each step was split up into its own macro. This way you can check the macro’s work each step to make sure its doing what it should. It’s by no means perfect but it is a major time saver. I’ve in particular noticed it sometimes confuses integers with dates because excel often stores dates as integers. So I always check dates to verify they are correctly being identified.
The spread sheet is set up to list out each column name, data type, some flags about the data that determines the type, and a brief example of the content. This really helps to verify each column’s intended data type. There is a Has Null flag that helps you know if the data has null values. There is a count and count distinct to further provide insight. This is particularly useful if you are trying to conserve memory and want to substitute a column that only has a few values types to instead be a pseudo column.
Another feature I added was to change the headers to be SQL friendly since some of the exports I feed the macros has headers with spaces or other special characters. The VBA simply replaces them with a substitute such as the underscore character.
You’ll also notice a fourth macro called Delete SQL. This is an additional tool I added that reads through the given file and creates a delete statement for all entries in the file. Its helpful in particular if you need to reread data in after discovering an issue or have a newly updated list.
