MSSQL – Data Transfer

Recently I had to transfer ICD9CM codes from one database to another with limited database permissions, I decided to let the database work for me to perform the task.

Ideally you’d have Visual Studio SSIS or some other mass data transfer option but this query will help you to get the data across.

One thing to point out is the replace command for [shortdesc] and [desc], this replace command is helpful if your varchar data type fields have a single quote in them.

SET IDENTITY_INSERT icd9cm ON
GO
SELECT 'INSERT INTO icd9cm (id, name) VALUES (''' AS insert1
	,[id]
	,''',''' AS insert2
	,[group_id]
	,''',''' AS insert3
	,[pricode]
	,''',''' AS insert4
	,[seccode]
	,''',''' AS insert5
	,[code]
	,''',''' AS insert6
	,REPLACE([shortdesc], '''', '''''')
	,''',''' AS insert7
	,REPLACE([desc], '''', '''''')
	,''');' AS insert8
FROM [icd9cm]