.gitignore

*.sql
*.sed

rachel_convert_sql.py

import re

re_all = re.compile('(INSERT.*?)(?=\n(INSERT|\Z))', re.MULTILINE | re.DOTALL)
re_values = re.compile('^INSERT \[dbo\]\.\[(.*)\] \((.*)\) VALUES \((.*)\)', re.MULTILINE | re.DOTALL)

# File to read
with open('dbo.DDR_Q.Table.sql') as infile:
    # File to write
    with open('dbo.DDR_Q.Table.p.sql', 'w') as outfile:

        f = infile.read()

        inserts = re.finditer(re_all, f)
        i = 1
        for insert in inserts:

            s = insert.group(0)

            # Match and replace the MS crap
            m = re.match(re_values, s)
            if (m):
                table_name =  m.group(1)
                column_names = re.sub('[\[\]]', '', m.group(2))
                values = m.group(3)
                values = re.sub('CAST\(([0-9\.]*?) AS Decimal.*?\)\)', '\g<1>', values)
                values = re.sub('CAST\(N(\'[0-9-.: ]*?\') AS DateTime\)', 'TIMESTAMP \g<1>', values)
                values = re.sub(', N\'', ', \'', values)
                values = re.sub('^N\'', '\'', values)

                # Build the statement
                stmt = 'INSERT INTO %s (%s) VALUES (%s);\n' % (table_name, column_names, values)
                # Write it to the output file
                outfile.write(stmt)

            # Print inserts that are problematic
            else:
                print insert.group(0)
                exit(1)