« Apple application error | Main | Oracle Calendar with Palm conduit won't sync »

Inserting multiple rows in SQL Server and moving data from MySQL

Inserting multiple rows of data in a single SQL statement should be easy. MySQL makes it easy. You simply separate your rows with commas:
INSERT INTO MyTable (FirstName, LastName, BirthYear)
  VALUES ('John', 'Johnson', 1980), ('Pete', 'Peterson', 1981), ('Pradeep', 'Pradeepson', 1982);
Naturally, you can't use this syntax in SQL Server. That'd be too easy! Maybe someday Microsoft will get around to supporting it. For now, you have to do something like this:
INSERT INTO MyTable (FirstName, LastName, BirthYear)
SELECT 'John', 'Johnson', 1980
UNION ALL
SELECT 'Pete', Peterson', 1981
UNION ALL
SELECT 'Pradeep', 'Pradeepson', 1982
The first version of this query would be especially useful if you're moving from MySQL to SQL Server Express, which doesn't offer any built-in way of importing data (which even Microsoft Access supports). Why would you want to do this? My motto is, "if it ain't broke, break it." Maybe you want to learn SQL Server. So instead, you could try a mysqldump on the table you want to export:
mysqldump -u root -p MyDatabase Names > Names.txt
You'll wind up with a complete catalogue of the structure and contents of the Names table. Assuming the table structure already exists in MSSQL, the only part of the file we're interested in is the INSERT statement, which looks like this:
INSERT INTO `Names` VALUES 
  (1,'John','Johnson','1980'),(2,'Pete','Peterson','1981'),(3,'Pradeep','Pradeepson','1982');
But as noted earlier, this won't work in MSSQL. Rather than attempting some nasty find-and-replace tactics, I think there's an even better and easier solution: the INTO OUTFILE function. This is used in a SELECT statement and will write the results of the query to a file. We can get very close to the SQL statement that MSSQL needs using this technique. Once again, we'll assume the table structure already exists, and that you've created your auto-incrementing and primary key ID column. In other words, we won't be importing the ID column; MSSQL will create that data for us. Here's the query I developed:
USE MyDatabase;
SELECT FirstName, LastName, BirthYear
FROM MyTable
ORDER BY FirstName, LastName, BirthYear
INTO OUTFILE 'DataOutput.txt' FIELDS ESCAPED BY '' TERMINATED BY ', ' 
  ENCLOSED BY '''' LINES TERMINATED BY '\r\nUNION ALL\r\nSELECT ';
Save this as a file called DataOutput.sql in MySQL bin directory and execute it like this:
mysql -u root -p < DataOutput.sql
This will write the file where the MySQL's data files are stored. It will return this data:
'John', 'Johnson', '1980'
UNION ALL
SELECT 'Pete', 'Peterson', '1981'
UNION ALL
SELECT 'Pradeep', 'Pradeepson', '1982'
UNION ALL
SELECT
Not bad! Obviously it isn't exactly what we want, but it would be pretty easy to make the corrections. Just add the INSERT INTO statement, add a SELECT to the first record, and remove the last SELECT. Then execute the query in MSSQL. Easy!