TSQL to generate JSON insert script for a collection with name same as sql server table


I was trying to migrate a SQLServer database to a MongoDB database with SSIS and to do this the SSIS transform expects an empty row in each collection in mongodb. I found this challenging because I had 50+ tables in the SQLServer database and I was supposed to insert a null row per collection. I played around with TSQL a little bit and came up with the below script. The script below generates an insert statement with empty values per table in SQLServer database.

with cte as(
select object_name(object_id) Table_Collection_Name,name from sys.columns where object_id in ( select id from sys.sysobjects where xtype='U'))
select Table_Collection_Name,InsertStatement from ( 
            'db.'+Table_Collection_Name+'.insert([{' + STUFF((    SELECT ',' + SUB.name +':null'
                        -- Add a comma (,) before each value
                        FROM cte SUB
                        SUB.Table_Collection_Name = CAT.Table_Collection_Name
                        FOR XML PATH('') 
                        ),1,1,'') +'}])'
AS [InsertStatement] 
) x
group by 
Table_Collection_Name, InsertStatement
order by Table_Collection_Name, InsertStatement

With SQLServer 2016 you should be able to generate the JSON script from SSMS itself using ‘for Json’ option. You can find more information on this here

Hope this helps!

This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s