June 1, 2011 at 4:55 am
I've written my first SQL statement using Cursors and would like to share for review and comment.
I know that this is inefficient and cursors should be avoided whenever possible (because they are inefficient) but I don't know how else to move this data.
If you have suggestions how to make this better, please share.
SHORT DESCRIPTION (the detailed description is below):
------------------------------------------------------
I need a nightly job that copies 35k records from MSSQL to MySQL.
SOURCE TABLE DEFINITION ATTACHED: SourceTable.txt
------------------------------------------------------
MY CURSOR SQL TO BE REVIEWED: Below (and attached for convenience)
------------------------------------------------------
/*
THIS CURSOR WAS WRITTEN FROM THE EXAMPLE
FOUND HERE:
http://www.sqlservercentral.com/articles/cursors/65136/
*/
use AdventureWorksDW
GO
DECLARE @FirstName NVARCHAR(50)
DECLARE @LastName NVARCHAR(50)
DECLARE @Phone NVARCHAR(25)
DECLARE @EmailAddress NVARCHAR(50)
DECLARE @ListThisNumber TINYINT
DECLARE curPersonalPhones CURSOR FAST_FORWARD FOR
SELECT top 35000 replace(FirstName,'''','''''') as FirstName,replace(LastName,'''','''''') as LastName,Phone,EmailAddress from DimEmployeeTEST
OPEN curPersonalPhones
FETCH NEXT FROM curPersonalPhones
INTO @FirstName, @LastName, @Phone, @EmailAddress
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('insert into test.clientinfo (FirstName,LastName,Phone,Email) VALUES (''' + @FirstName + ''',''' + @LastName + ''',''' + @Phone + ''',''' + @EmailAddress + ''')') AT MYSQLDB
FETCH NEXT FROM curPersonalPhones
INTO @FirstName, @LastName, @Phone, @EmailAddress
END
CLOSE curPersonalPhones
DEALLOCATE curPersonalPhones
GO
-- END
Thanks for any feedback on this SQL code.
-Allen
LONG DESCRIPTION OF THIS PROJECT (the narrative version):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- I have an application that serves data to our internal users via an AJAX AutoComplete lookup.
- The data source is from our legacy VAX system.
- I have an automated job that signs into the VAX system, runs a report and sends that report to a TXT file in an FTP folder.
- I then have SSIS FTP and import the raw report data and clean it up by getting rid of page headers, add missing sequence data, etc.
- When my SSIS package is finished, my data is ready for user access
- I have an IntrAnet running WordPress (MySQL). I want to add this content for availability in WordPress. Since WordPress doesn't run under MSSQL very well, I need to make the date available to MySQL. I don't know how to create a "LINK SERVER" within MySQL or have MySQL query the MSSQL DB so I've decided to "push" the data from MSSQL to MySQL.
- I've created an MSSQL Link Server to the MySQL database and can easily do simple transactions, like "select * from table" or "truncate table" etc, but I can't figure out how to do a bulk insert. Which is why I am submitting the cursor for review.
June 1, 2011 at 5:56 am
Haven't played around with SQL Server -> mySQL too much, but firstly, I'm not sure there's a need for the cursor - couldn't you just do the following:
INSERT INTO MYSQLDB.test.clientinfo (FirstName,LastName,Phone,Email)
SELECT top 35000 replace(FirstName,'''','''''') as FirstName,replace(LastName,'''','''''') as LastName,Phone,EmailAddress from DimEmployeeTEST
It's not going to set the world on fire for performance (I suspect it's still going to be row by row) but it's at least easier to read.
The fastest way (although not always the most convenient) is usually to output the data to a flat file from SQL Server (either from SSIS or bcp) and import using mySQL's import tool
June 1, 2011 at 6:00 am
I've done data transfers like this to access, excel and dbase and while it wasn't has fast as sql server it was decent enough... especially if this is a 1 time import... or maybe daily load. Nobody cares that it takes 5-10 extra seconds when a job auto starts @ midnight.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply