From time to time, I need to transfer data from one database to another. RedGate Data Compare is my to tool of choice, but not all clients have a license for it ($495 for SQL Data Compare, $1795 for the Developer Suite). I also like SQL Database Compare if I need to sync DDL (tables, indexes, constraints, stored procs, functions, etc…) from one system and the other.
The other day, as a BizTalk developer, I had to copy a lookup table from one system to an existing database on another system. So I couldn’t just backup/restore the file. I tried some of the tools in SQL, which I must admit I don’t use often, and I was getting a connectivity issue; maybe the firewalls between or DMZ (not 100% sure).
Years ago, I remember a nice C# WinForm program that allowed you to enter a SQL statement, then would generate SQL Insert Statements from it. I was having trouble finding it, but found two other alternatives.
Solution #1 – All SQL – Generating SQL Statements in SQL Server – I like this one because it was all written in T-SQL. The site provides a StoredProc that you add to your database and execute with a table name. You turn off grid mode, i.e. put your output in text mode, and save or copy/paste the results less the header. This allowed me to get done what I needed this week in just about 10 minutes. I just took the generated SQL statements, copied them to the other server, and ran them in SSMS. To run it, you just run the stored proc and pass the table name:
exec InsertGenerator MyTableName
Out of the box, it doesn’t give you the ability to select a subset of rows with a where clause.
Solution #2 – C# – Generating SQL Statements in C# – This one is a C# solution if your prefer that language. It’s a console program, so no WinForm/GUI. I didn’t actually try this one. I’m still hoping to find the GUI version that I found a few years ago. I think it was on CodeProject as well.
In my case, it was a code lookup table used by a BizTalk map. I took the insert statements as well as the create table statement, and added to the BizTalk project (in a “doc” or “prereqs” subfolder so future developers on different environments would be able to load up the table quickly. You can include other files such as .sql or .txt into a BizTalk project, but they are not compiled of course. That way, they are checked into your source code repository.
Below is an example of a RedGate Data Compare. You can specify two databases, then it shows you the tables. You can select all tables or individual select the tables you want to compare. It then compares the rows of those tables. However, note that the table has to exist on both databases for it to compare the table and generate SQL Insert Statements. SQL Database Compare will help you to sync up the tables.
