0

I am linking/copying tables from mysql to SQL Server. I have the linked servers setup and I can copy the tables. I need to know how to update only new records from the mysql database because there are 400,000 records on one table. Here is my query

INSERT INTO kiosk_test.dbo.CDS_STU_CLASS 
FROM openquery(MYSQL, 'SELECT * FROM mycds.CDS_STU_CLASS')

How would you go about a stored procedure or query to update only the new records? I have tried researching the best I can to no real answer.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3253523
  • 1
  • 1
  • Please use the code formatting tools. Please elaborate on what is the actual output of the sql executed above, explain what the desired outcome is, and what you've tried in reaching the desired result. – Chris Marisic Aug 05 '14 at 19:24
  • 1
    How are "new records" determined? – Marcus Adams Aug 05 '14 at 19:30
  • The new records are updated from our district office into the mysql database every 5 minutes – user3253523 Aug 05 '14 at 19:34
  • Not related to your question, but you might want to use an ms-sql temporary table to receive the records from mysql. It all depends on how you want to handle simultaneous use of this query/procedure/whatever it will eventually be. – Dan Bracuk Aug 05 '14 at 19:34
  • Is there a datetime field in the mysql table that can be used to filter the results? – Dan Bracuk Aug 05 '14 at 19:34
  • There is a column called m_ts for the database time stamp. It is formatted as datetime. – user3253523 Aug 05 '14 at 19:41

1 Answers1

0

From Do I have to use OpenQuery to query a MySQL Linked Server from SQL Server?:

I think you can change openquery(MYSQL, 'SELECT * FROM mycds.CDS_STU_CLASS') to mycds..CDS_STU_CLASS

Then maybe this will work:

INSERT INTO kiosk_test.dbo.CDS_STU_CLASS 
(SELECT _columns_ 
 FROM mycds..CDS_STU_CLASS new
 LEFT JOIN kiosk_test..CDS_STU_CLASS old 
     ON new.recordID = old.recordID
 WHERE old.recordID IS NULL)

Untested...

Community
  • 1
  • 1
bf2020
  • 742
  • 4
  • 7