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

Hello,

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 ( 
SELECT     
         Table_Collection_Name,
            'db.'+Table_Collection_Name+'.insert([{' + STUFF((    SELECT ',' + SUB.name +':null'
                        -- Add a comma (,) before each value
                        FROM cte SUB
                        WHERE
                        SUB.Table_Collection_Name = CAT.Table_Collection_Name
                        FOR XML PATH('') 
                        ),1,1,'') +'}])'
AS [InsertStatement] 
FROM  cte CAT
) 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!

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s