BookmarkSubscribeRSS Feed
Azeem112
Quartz | Level 8

Dear Community,

 

I've two servers. first one is has SQL server and second one has Mysql database server. I want to move and update data from SQL server database to Mysql database daily basis using SAS data integration studio. Both databases have same schema. For this purpose

 

1) I created an ODBC connection.

2) Added an ODBC server in SAS Management Console.

3) Created a ODBC library attached with my server.

4) Registered the tables in my ODBC library

 

Now I can access data of first server at second server. Now I want to update mysql database. For this I've to write a job who gets data from my ODBC library and update mysql database tables. I'm having issues that how can I do this? 

5 REPLIES 5
Patrick
Opal | Level 21

@Azeem112

"Now I can access data of first server at second server

If using SAS then the path would be SQL Server -> SAS .> MySQL and you would need two ODBC connections/Servers defined in SAS Metadata - one for the source and one for the target.

 

Not sure what "Now I can access data of first server at second server" means. You can access the SQL tables from MySQL via an ODBC connection? If so then SAS is not involved and whatever you've defined in SAS Metadata doesn't come into play. ....or you now can access the SQL Server from SAS? If so then you need to do the same game for the target server.

 

If you want to use DIS then define both servers and table metadata for source and target and then a simple table loader should do the job.

Azeem112
Quartz | Level 8

I can access SQL data in SAS studio and in SAS Management Console but I can't simply write a table loader because there are 30 tables whose data i want to transfer. First I want to copy all data from SQL to Mysql then update mysql tables from SQL with recent data of daily basis. 

LinusH
Tourmaline | Level 20

If you need a solid design, you need an appointed resource to help you.

We can just give answers which are quite limited to the specific question at hand, which in your case was quite wide.

If you go with a direct connection between the databases you can still maintain the metadata in SAS, but it becomes a bit more awkward, especially for the job (since you probably need explicit pass-through logic to define the load logic).

Data never sleeps
DavidPhillips2
Rhodochrosite | Level 12

What settings does the final table written back to a database need to have write to the actual database?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1483 views
  • 0 likes
  • 5 in conversation