When working with tables that have over 256 columns, or at any rate a 'large number', whatever that means to the developer, one can employ strategies for getting things done expeditiously. At first look restructuring large tables appears to require a lot of hand coding. Two techniques are described that handle such problems with incidental amounts of work.

The Circuitous Route

Enterprise Manager Table Designer and Microsoft Access don’t like to construct tables with more than 255 columns, although SQL-Server can have up to 1024 columns per table. Creating and modifying tables with more columns involves right-clicking a table in Enterprise Manager, selecting ‘All Tasks’, and then selecting ‘Generate SQL-Script’. Clicking the Preview button generates the table creation script, which can be copied onto the clipboard. This can be ‘pasted’ into Query Analyzer, modified, and the modified script can be executed. However, this method of modifying the table destroys everything in it, since the table is dropped and then recreated.

If the developer intents to insert a column in the middle of this table (rather than at the end) transferring the existing records becomes problematical. The solution to this is to create a view in with the old table is the source, and the column order matching the new. Doing this for 300 or more columns, however, is a lot of hand coding. Not.

‘Paste’ the script that has been copied to the clipboard into Notepad, and save the file as a text file. While the text file is open in Notepad, remove every line that is not a column definition: i.e. (‘Create table’, ‘) go’, ‘if exists...’, etc.). Save only the column definitions, nothing else.

Launch Excel, and open this text file in Excel, which will trigger an import wizard. Leave the delimited text button selected. Click Next.

Change the checked delimiter to Space. Everything else should be left alone. In particular, the option ‘Treat consecutive delimiters as one’ should remain selected. Click Next, then click Finish.

Now Excel has a spreadsheet of column names, with the actual names in Column B. Highlight all the other columns and clean them out. Then, using Find, Replace each ‘]’ (close bracket) with ‘],’ (close bracket followed by comma). Use ‘Replace All’. If you have a region selected it will only apply changes to that region, so make sure no region is selected. You could remove the trailing comma from the last column now, or do it in one of the following steps.

Highlight the column with the table names and copy it to the clipboard. Switch to Enterprise Manager and create a new view. In the SQL pane, there is a ‘Select’ followed by a ‘From’ on the next line. Place the cursor after the Select statement and ‘paste’ the clipboard into this location. After that, put the name of the original table following the From. Presto, you now have a view of that table.

From here, it is trivial to insert new columns or move the exiting columns around. The end result of this should be a view of the old table where the column arrangement matches that of the new table.

The final step to this is the following one time SQL statement:

Select * into NewTable from ViewOfYeOldeTable;

An Easier Way

Having cobbled together the above approach, I went back to the Designer in Enterprise Manager to see if there are facilities for doing this without bringing in Notepad, Excel, etc. Turns out there is.

Start by creating a new View, whereupon you get four windows, one of which has the SELECT ... FROM. Use the “+” icon on the menu bar to bring up a list of tables. Select a table and drag it into the top window. When right-clicking on the title bar of this table, a list of options is presented, one of which is ‘Select All Columns’. Select this option.

All columns in the table are now inverted, meaning they are selected for editing, but nothing else has changed. Click on the ‘*’ (All Columns) checkbox. Now all columns are selected for inclusion in the view.

What one notices immediately is that this is a mess. Having selected All Columns and then each column individually, the individual columns have been renamed to Expr1, Expr2, ... etc. Useless, useless, useless. Uncheck all columns, removing all columns from the view.

Hold the Control key down while clicking on the * (All Columns) row at the top of the list. Do not click the checkbox; click the text of the column definition itself. What happens now is that all the other columns remain selected, but the first one is not. Click on the checkbox next to any of the selected individual columns. All are selected, and a view is composed without renamed columns.

Insert a new column in the middle of this view. My expression for inserting a Varchar, for instance, is Convert(varchar(65), null) as NewVarcharColumn.

Save the view. An example might be vwYeOldeTable, or something to that effect.

In Query Analyzer, run the following expression:

Select * into NewTable from vwYeOldeTable

This creates the new table, with the old and new columns arranged as defined in the view.

Select ... Into is a configurable option in SQL Server. If it is turned off Select Into will only work with temp tables (i.e., #temp1, ##globaltemp2). If you’re reading this, however, you probably have the wherewithal to address that concern.

Why Describe Both?

In certain cases getting the column list into Notepad and Excel may be helpful if the conversion is between databases, such as from SQL to Foxpro or SQL to MySQL. These intermediaries may be useful when switching between disparate toolsets.