Data Migration 101: Generate a SQL Script with Table Data in SQL Server

Recently, I found myself in a situation where I needed to migrate data from one table to another on different servers. The tables were exactly the same but located on servers without a linked server connection, and creating one wasn’t doable at the time due to privilege issues. What should have been a simple task turned into an hour-long manual task and frustration. Here’s what happened and the invaluable lesson I learned along the way.

I had a relatively small set of data — just 50 rows across 8 columns that I needed to move to a structurally identical table on a different database. Thinking it wouldn’t take long, I chose what I thought was the simplest route: exporting the data into Excel (with headers), tweaking it in Excel, and creating an SQL insert statement from there. However, the tweaking process ended up requiring me to fix spaces, and commas, handle null values, and resolve other formatting issues. It was quite frustrating, and I couldn’t stop thinking that there must be a better way than manually formatting everything in Excel — but I ignored that feeling (mistake number one) and continued working as I had started. After an hour, I finally managed to insert the data into the table.

A few days later, similar task, but this time I really didn’t want to go through the same manual process…