Use MySQL linked server with dynamic db name

Question

I have a table category, which exists in both test and prod db. Whenever I have to get data from MySQL I have to write test or prod in db like below, as openquery doesn't support dynamic query.

SELECT  * 
    INTO #Category
            FROM 
    OPENQUERY([MYSQLLINKEDSERVER], 'SELECT * from test.category') category

I have tried it with execute command, as it worked with dynamic query.

declare @storeId as integer = 1

declare @query nvarchar(max) ='SELECT * from test.category where id = ?' 
exec(@query, @storeId) at [MYSQLLINKEDSERVER]

but the issue is, I have to create temporary table every time whenever I have to get data, I have multiple db and multiple tables, I can't create temp table every time.

My problem is, I need a way in which I passed a variable like declare @t nvarchar(250) = 'test', and passed a query select * from category and it fetch data from MySQL test db and stored it in temp table as given below

select * into #category
 openqueryOrSOmelogic('select * from category','test')

I don't want to provide schema for temp table and provide db name from variable.

Is there any way to achieve it?

You can use 4-part names perhaps? SELECT * from MYSQLLINKEDSERVER.test..category where id = 4 or whichever syntax mysql supports, i don't remember if schema goes first or last — siggemannen
– siggemannen, Commented Oct 14, 2024 at 19:10
@siggemannen I can able to extract data by both approach, issue is I doesn't want to specify temp table schema and also retrieved data based on dynamic db name. first approach doesn't provide functionality to get data usgin dyanmic db name, second approach doesn't provide functionality to get data in temp table without specifying schema. — Faraz Ahmed
– Faraz Ahmed, Commented Oct 14, 2024 at 19:16
Sounds kinda weird, but ok, you can create a view which is a UNION ALL of both test and prod with a column specifying where data is from and then go to town — siggemannen
– siggemannen, Commented Oct 14, 2024 at 19:19

Mohammed Faruk · Accepted Answer · 2024-10-14 19:05:39Z

0

You can select one database dynamically (i.e. test or prod) in a query using OPENQUERY with dynamic SQL sp_executesql. You will need to construct the query dynamically and then run it, as OPENQUERY does not support dynamic queries.

Follow an example:


DECLARE @t NVARCHAR(250) = 'test'; — Database name eg : ( test or prod )

DECLARE @query NVARCHAR(MAX);

DECLARE @storeId int = 1; -- optional filter

SET @query = 'SELECT INTO #Category FROM OPENQUERY([MYSQLLINKEDSERVER], '' SELECT * FROM '+@t+'. category WHERE id = ' + CAST(@storeId AS NVARCHAR(10))===';

EXEC sp_executesql @query;

With this solution, there will be no headache of creating the temporarily table schema whenever you change your database. It also supports filtering with @storeId or any other parameter.

answered Oct 14, 2024 at 19:05

Mohammed Faruk

3611 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Faraz Ahmed Over a year ago

temp table is not supporting in dynamic query, so how to get data in temp table after EXEC command?

Mohammed Faruk Over a year ago

For getting data into a temporary table after an EXEC call, you can use: 1. Global Temp Table:

sql SET @query = 'SELECT INTO ##Category FROM OPENQUERY([MYSQLLINKEDSERVER], ''SELECT * FROM ' + @t + '.category'')'; EXEC sp_executesql @query; SELECT * FROM ##Category; DROP TABLE ##Category;

2. Pre-create Temp Table:

sql CREATE TABLE #Category (id INT, name NVARCHAR(255)); SET @query = 'INSERT INTO #Category SELECT * FROM OPENQUERY([MYSQLLINKEDSERVER], ''SELECT * FROM ' + @t + '.category'')'; EXEC sp_executesql @query; SELECT * FROM #Category;

Joel Coehoorn Over a year ago

I know you may not have that access, but if it were me I would set up the linked server twice, once for test and once for production.

Mohammed Faruk Over a year ago

Yes, that suggestion is a good one! Setting up two linked servers, one for test and one for production, makes things much easier. You won’t have to deal with dynamic database selection. Instead, you can reference each environment directly in your queries. ex: - For test: sql SELECT * INTO #Category FROM OPENQUERY([MYSQLTESTLINKEDSERVER], 'SELECT * FROM category'); - For production: sql SELECT * INTO #Category FROM OPENQUERY([MYSQLPRODLINKEDSERVER], 'SELECT * FROM category'); This way, you avoid the complexity of dynamic SQL and keep it straitfrwd

Faraz Ahmed Over a year ago

@MohammedFaruk by getting data in global temp table, it will not worked for multiple connections.

|

Collectives™ on Stack Overflow

Use MySQL linked server with dynamic db name

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related