SQL ADO Query

ajayjain · 2014-04-30

Hi,
I am writing a small functionality into Navision which will transfer data from 1 db to another db using ado
user will be able to configure himself, which tables and filters etc. like sales order for this department should be copied into another db.
it will generate sql script and execute.
It works fine but....getting some issues like
if in table 'Payment Terms' I got 5 records and other db has 3 then it should transfer only 2 records.
this should also work on SO, PO etc. which has different primary keys
Any suggestion?
I don't want to loop through records and update 1 by 1
Also timestamp is causing problem so I have to add fields names in sql script
Any suggestion please

SiSt · 2014-05-02

Hi,

I don't think it is a good idea to do it directly on the SQL level. I would use the webservices and validate everything in the second database, if possible. If you can't use the webservices I would still use a simple file based export and import and try to automate that using an NAS. (Or use an add-on that does exactly that for you)

If you want to use SQL and only update the records that does not exists you can use a where clause on the INSERT statement (if it is an UPDATE it works too). You can find a lot of examples if you google for conditional insert or 'NOT EXISTS' and T-SQL.

SQL ADO Query

Comments

Categories