A set of regex to help transform a schema / data export from MS SQL Server to sqlite

mssql-to-sqlite.sed

# schema.sql transforms
s/\[n?(var)?char\]\((max|\d+)\)/TEXT/
s/\[(small|big|tiny)?int\]/INTEGER/
s/\[bit\]/BOOLEAN/
s/\[datetime\]/DATETIME/
s/\[(var)?binary\]\((max|\d+)\)/BLOB/
s/^\t\[([^\]]+)\]/\t\1/
s/IDENTITY\(\d+,\d+\) //
s/CONSTRAINT \[[^\]]+\] PRIMARY KEY CLUSTERED \n\(([^\)]+)\)/PRIMARY KEY (\1) /
s/ ASC,?\n\t?/, /
s/, \) WITH.*\n.*;/;/
s/NOT FOR REPLICATION //

# data.sql transforms
s/\[dbo\]\.\[([^\]]+)\] /\1/
s/( \(|, )N'/\1'/
s/( \(|, )\[/\1/
s/\](\)|, )/\1/
s/\)$/\);/
s/CAST\(N'(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+)' AS DateTime\)/strftime\('%Y-%m-%dT%H:%M:%f','\1'\)/

# note this one is for converting BLOBs, and can hit the contents of fields, you may want to skip it or clean up manually
s/, 0x([\dA-F]+)/, X'\1'/

s/^INSERT /INSERT INTO /
s/^(INSERT.*\))$/\1;/
s/\)\nINSERT /\);\nINSERT /
s/SET IDENTITY_INSERT .*//
s/\\' \+ N'\n//

I got a .bak file of an MS SQL Server database that I needed to work on, and for convenience sake I decided to convert it to sqlite. I installed a vm with Windows 10, and SQL Server Express 2017, then performed a restore of the .bak file into it. After installing the SQL Server Management Studio I followed the exporting step in https://serverfault.com/questions/147638/dump-microsoft-sql-server-database-to-an-sql-script. In particular:

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

I took that .sql file and split it into a schema.sql and data.sql then applied the above transforms and a small number of hand corrections, including adding to the top of the data file a PRAGMA journal_mode=wal;

sqlite3 dump.db < schema.sql
sqlite3 dump.db < data.sql

I hand corrected the small number of cases where an embedded , 0xE0 had accidentally been transformed to , X'E0' and ended up with a working database that I could script against without needing a full vm running.

I'd dumped all the FOREIGN KEY constraints as I just didn't need them, I'm extracting data from this DB, not actually transforming any of the data in it.