Foliotek Developer Blog

Simplifying Manual Insertion of Static Data from one Database to another

Problem Description

We’ve all run into the issue. You have created a look-up table on one database, perhaps a test database. Now it’s time to take it live, and you need to port all the rows from the table in the test database into the corresponding table on the live database. Sure, you could use SQL Data Compare or various tools like that, but this table only has 10 rows, so is it really worth the trouble?

In my case, I had to port data into two live database instances, so I didn’t want to have to go through a long process. We do have SQL Compare tools, but need to upgrade them for SQL Server 2012, and I didn’t want to have to wait.

Of course, if the databases were on the same server, then copying between servers is pretty simple. However, if the databases are separated by type (i.e. Oracle and SQL Server), then that is much more difficult.

Let me use a standard User table as an example for simplicity, although that really does not fit with what you would use this for.

The Brute Force Method

What I initially did was to just select everything from one table, paste it into a new query window in SQL Server Management Studio, then add commas and tick marks to insert each row manually.

 SELECT * FROM Users  
 UserID Email FirstName MiddleName LastName IsImpeached  
 1 george@presidents.gov George Washington 0  
 2 john@presidents.gov John Adams 0  
 3 thomas@presidents.gov Thomas Jefferson 0  
 4 james@presidents.gov James Madison 0  
 5 jamesmonroe@presidents.gov James Monroe 0  
 6 johnq@presidents.gov John Q. Adams 0  
 7 andrew@presidents.gov Andrew Jackson 0  
 8 martin@presidents.gov Martin Van Buren 0  
 9 william@presidents.gov William Henry Harrison 0  

Next, I started creating the INSERT statement. This necessitated the insertion of a lot of commas and ticks and parentheses. This is tedious, to be sure, but it does work, and it isn’t all that bad for a limited number of rows.

 INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (1, 'george@presidents.gov', 'George', '', 'Washington', 0)  
 2 john@presidents.gov John Adams 0  
 3 thomas@presidents.gov Thomas Jefferson 0  
 4 james@presidents.gov James Madison 0  
 5 jamesmonroe@presidents.gov James Monroe 0  
 6 johnq@presidents.gov John Q. Adams 0  
 7 andrew@presidents.gov Andrew Jackson 0  
 8 martin@presidents.gov Martin Van Buren 0  
 9 william@presidents.gov William Henry Harrison 0  

After that, I could copy the Insert statement from the beginning to the first parenthesis after the VALUES keyword and then add all the commas and ticks. Still tedious, but not all that bad if you’re just wanting to complete the task manually.

A Slightly Less Taxing Brute Force Method

It occurred to me somewhere in this process that there is a fairly easy way to make this brute force method slightly less taxing, and I don’t know why this hadn’t occurred to me before. Perhaps it is one of those things that does not seem obvious until you see it for the first time. Anyway, the main effort comes in adding all the parentheses, commas and ticks, so why not have the system generate that for us?

All I did was to modify the original select query as follows:

 SELECT '(' + CAST(UserID AS VARCHAR(25)) + ', '" + Email + "', '" + FirstName + "', '" + MiddleName + "', '" + LastName + "', ' + CAST(IsImpeached AS CHAR(1)) + ')'  
 FROM Users
 (1, 'george@presidents.gov', 'George', '', 'Washington', 0)  
 (2, 'john@presidents.gov', 'John', '', 'Adams', 0)  
 (3, 'thomas@presidents.gov', 'Thomas', '', 'Jefferson', 0)  
 (4, 'james@presidents.gov', 'James', '', 'Madison', 0)  
 (5, 'jamesmonroe@presidents.gov', 'James', '', 'Monroe', 0)  
 (6, 'johnq@presidents.gov', 'John', 'Q.', 'Adams', 0)  
 (7, 'andrew@presidents.gov', 'Andrew', '', 'Jackson', 0)  
 (8, 'martin@presidents.gov', 'Martin', '', 'Van Buren', 0)  
 (9, 'william@presidents.gov', 'William', 'Henry', 'Harrison', 0)  

As you can see, this reduces the effort for the brute force method quite significantly, even to the point that it might not be able to be called the brute force method anymore. All that has to be done is to copy the the part of the INSERT statement that precedes the actual values.

Now I'm Just Being Lazy

Someone once suggested that innovation sometimes comes just because we are lazy. I know what that means–we're not really lazy, we just want to do more interesting things, so we automate the simple. It just occurred to me that this could be simplified even more by including the "INSERT INTO… VALUES prefix" into the SELECT statement. This tactic eliminates all manual addition of commas, parentheses, ticks and everything else. Then all I have to do is copy all the rows, paste them into a query window and run it.

 SELECT 'INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached )  
 VALUES (' +  
 CAST(UserID AS VARCHAR(25)) + ', '" + Email + "', '" + FirstName + "', '"  
 + MiddleName + "', '" + LastName + "', ' + CAST(IsImpeached AS CHAR(1)) + ')'  
 + '  
 '
 FROM Users  
 INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (1, 'george@presidents.gov', 'George', '', 'Washington', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (2, 'john@presidents.gov', 'John', '', 'Adams', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (3, 'thomas@presidents.gov', 'Thomas', '', 'Jefferson', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (4, 'james@presidents.gov', 'James', '', 'Madison', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (5, 'jamesmonroe@presidents.gov', 'James', '', 'Monroe', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (6, 'johnq@presidents.gov', 'John', 'Q.', 'Adams', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (7, 'andrew@presidents.gov', 'Andrew', '', 'Jackson', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (8, 'martin@presidents.gov', 'Martin', '', 'Van Buren', 0)

INSERT INTO Users (UserID, Email, FirstName, LastName, MiddleName, IsImpeached)  
 VALUES (9, 'william@presidents.gov', 'William', 'Henry', 'Harrison', 0)

Conclusion

So, obviously, if you have SQL Data Comparison tools the automate this, that is preferable. However, if that is tedious to set up, or if you don’t have access to such tools, this can make copying rows for small tables much less tedious.

Note

You will probably have to add "SET IDENTITY_INSERT Users ON" at the beginning and "SET IDENTITY_INSERT Users OFF" at the end.

A few of my other favorite posts