quote:

---

Originally posted by ninjatill

I have an AIX mainframe that I replicate data from every night. The data gets dumped, unaltered, using a linked odbc connection to the AIX box. The problem I'm having is that the mainframe software automatically creates a new invoices table every year... it simply adds a character in the middle of the table name to differentiate. The developers stupidly also add that character to the column name of each column in the table. This makes it pretty hard to do a UNION ALL between the yearly tables and get one big table. Is it possible to loop through each table and dynamically rename them to remove the character.

For example:
Invoices for 2010 are stored in table: B1F_REC
Invoices for 2011 are stored in table: B1G_REC

B1F_REC has fields: B1F_INVNO, B1F_DATE, B1F_COST, B1F_SELL, etc.
B1G_REC has fields: B1G_INVNO, B1G_DATE, B1G_COST, B1G_SELL, etc.

Each table has the exact same fields but with that character. There are about 50 total fields. It would suck to hard-code a bunch of UNION ALL statements with column aliases when I have to add a new table every year. Right now we are using SQL Server 2010 Express but after we get this up and running, we're going upgrade. I hear the full version as SSIS which may be able to some data transformations but is there a way to just rename the columns in the tables for now?

Any thoughts/suggestions appreciated.

---

SELECT SQL_CODE FROM (
SELECT 'SELECT ' + '''' + SUBSTRING(COLUMN_NAME,1,3) + '''' + 'AS SOURCE ' AS SQL_CODE
, TABLE_NAME, 2 AS SQL_ROW, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Columns
WHERE ORDINAL_POSITION = 1
UNION ALL
SELECT '  ' + COLUMN_NAME + ' AS ' + SUBSTRING(COLUMN_NAME,5,LEN(COLUMN_NAME) - 4)
, TABLE_NAME, 3 AS SQL_ROW, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Columns
WHERE ORDINAL_POSITION = 1
UNION ALL
SELECT '  ' + COLUMN_NAME + ' AS ' + SUBSTRING(COLUMN_NAME,5,LEN(COLUMN_NAME) - 4)
, TABLE_NAME, 3 AS SQL_ROW, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.Columns
WHERE ORDINAL_POSITION <> 1
UNION ALL
SELECT 'UNION ALL ' AS SQL_CODE
, TABLE_NAME, 4 AS SQL_ROW, 1 AS ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES
) AS XXX
WHERE TABLE_NAME IN (
   'B1F_REC'
 , 'B1G_REC')
ORDER BY TABLE_NAME, SQL_ROW, ORDINAL_POSITION