October 11, 2016 at 5:17 pm
Hey guys,
I'm trying to insert into a linked server table from sql server (to mysql). I can do this with openquery but it takes forever. I read on the following link that you can make this much faster by doing an Execute ('...') AT MYSQL<linked server> using a table variable.
Here is the question and response that gave me the idea: http://www.sqlservercentral.com/Forums/Topic1640834-1550-1.aspx
I'm just struggling with the syntax...and I'm so close I think. Here is what I have so far:
declare @tbl_test TABLE(
ID int NOT NULL,
pub smallint NULL
);
insert into @tbl_test
SELECT ID
,pub
FROM dbo.tbl_live
declare @test-2 varchar(max) = 'Insert into tbl_mysql select * from ' + @tbl_test
EXECUTE(@test) AT MYSQL
This gives me the error: Must declare the scalar variable @tbl_test. I also tried putting the variable inside the single quotes...obviously it doesn't get recognized as a variable and when trying to pass to mysql...it has no idea what that is.
Anyone have the idea of the sytnax to get this working?
October 12, 2016 at 8:19 am
26 views and no hits.
Maybe I should explain a little more.
The mysql box is offsite and not ours...but they will be using our data and we have a close relationship with them. We have the option, of course, for exporting to text files but it feels like having a middle man...and would like the ability to push whenever we wanted...but to start it will be nightly.
Given the forum post that I posted above....it seems like if I can get this version to work it will be much faster than openquery. I've sent the guy who responded at the end a pm...but I can't even see that pm in my inbox.
October 12, 2016 at 8:43 am
Your table variable does not exist in the @test-2 variable. To use the the table variable in @test-2, your variable declaration must also be included in the set statement for @test-2, as well as the insert of the data to populate the table variable. That is more trouble than it is worth.
Before you execute your dynamic sql, you should select/print it and see if it produces runnable code. If you can't paste the output into a new query window and run it, it will not work in an Execute.
I recommend you use SSIS to copy data across to mysql, especially if this is a recurring load process. Inserting data in bulk across a linked server will be excruciatingly slow, during which time your source table will likely be locked from any updates. If the link server experiences any connection issues, your insert may rollback completely and need to start again.
You can get away with small inserts (e.g. single record) across a linked server, but even that will experience a significant performance hit versus a local table. Linked servers were not intended for bulk table loads.
Wes
(A solid design is always preferable to a creative workaround)
October 12, 2016 at 8:54 am
The reason it is failing is that the variable doesn't exist on the MYSQL side. When you do the execute AT command, you are telling it to run that string on the specified server.
By having that table variable outside of the varchar quote, you are then trying to cast it to a varchar which fails (casting a table to a varchar will fail).
So, for your example (experts correct me if I am wrong), I think you would need to use a cursor (I said the dreaded C word, so I think there is likely a better way to do this) and insert things 1 row at a time.
So quick (ie untested) code:
DECLARE @ID INT;
DECLARE @pub SMALLINT;
DECLARE @test-2 VARCHAR(MAX);
DECLARE cur CURSOR
FOR
SELECT ID ,
pub
FROM dbo.tbl_live;
OPEN cur;
FETCH NEXT FROM cur
INTO @ID, @pub;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @test-2 = 'INSERT INTO tbl_mysql VALUES ('
+ CAST(@ID AS VARCHAR(100)) + ',' + CAST(@pub AS VARCHAR(100))
+ ')';
EXECUTE (@test) AT MYSQL;
FETCH NEXT FROM cur
INTO @ID, @pub;
END;
I think that should work. Syntactically it is all correct (according to SQL), but if you are going to fire that, I'd do it on a test DB first as it is code from the internets and that can be scary.
But some of the other SQL experts may have a better way to tackle this.
EDIT: Called it. A SQL expert gave a better solution. I agree with whenriksen; SSIS is a better tool for migrating this data. I think my solution will work, but SSIS will be tons faster, just make sure your physical box has enough memory as SSIS pacakges do not run inside of SQL Server's memory space. That was one snag we have hit with SSIS before. If it starts paging to disk, it makes our 5 minute job take hours (one time it ran for over 5 hours before I cancelled it).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2016 at 12:41 pm
I'm actually having a difficult time with SSIS in this matter. If I do the odbc destination...its super slow. And I'm struggling to figure out why the OLEDB destination is failing. I believe it has something to do with this article (as I'm getting the same error):
https://blogs.msdn.microsoft.com/mattm/2009/01/07/writing-to-a-mysql-database-from-ssis/
The problem is is ssdt 2012 it only has an execute t-sql statement task that doesn't allow me to use the same OLEDB connection as the location to run the code (it must be sql server)
October 12, 2016 at 1:35 pm
I expect you'll have better end results trying to resolve your issues with SSIS/MySQL than using a linked server to bulk insert.
If you really must use the linked server for the insert, you'll need to generate the full insert statement from your data.
Declare @sql nvarchar(max) = N'';
SELECT @sql = @sql + N'INSERT INTO MyRemoteTable (ColA,ColB,ColC,ColD),VALUES(' + col1 + N',' + col2 + N',' + col3 + N',' + col4 + N');'
From MyLocalTable;
The dynamic SQL must be runnable on your MySQL database. Once you have that built out, you can Execute the @sql across the linked server. Linked servers are most useful for small volumes of data. Depending on the number of records, you may need to chunk it to prevent sending a massive insert statement across the wire in one go.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply