Move data from staging to production
-
I need to take DTS_companies (database DTS) and Update MAXSYR_companies (database MAXSYR) where records in DTS)companies are not in MAXSYR_COMPANIES.
I know how to make the connections to the 2 databases, but not how to move the data I want using a Transform Data Task. Should I be using an AxtivX script? I would appreciate any help.
Joan
-
There is no need to use an activex task unless you want to enrich or change the data you are copying in.
Just write your query inside a Transform Data Task between the 2 connections.
Example, 'Select columns... from DTS_companies where not exists (select * from
MAXSYR_COMPANIES)'
Then you can match the columns up on the transform tab.
------------------------------
The Users are always right - when I'm not wrong! -
Thanks for your reply.
I wrote the query on the Source page of the Transform data task and this returns an error of
ADO error: Invalid Object Name MAXSYR_COMAPNIES
Statement(s) could not be prepared
Deferred prepare could not be completed
The source is DTS_COMPANIES
the target is MAXSYR_COMPANIES
did I write the query on the proper tab of the Transform Data Task?
Regards,
Joan
-
Joan,
The Source tab is the correct place to write the query. Try qualifying the table names with the database and owner like this: database.owner.table.
You didn't say so, but I'm assuming the databases are on the same server. If they're on two different servers, you'll have to qualify the table names with the server name, also.
Greg
Greg
-
Greg,
Thanks for your reply. Yes the databases are on the same server. I tried your solution. This statement parses but shows nothing in the preview of the query.
It executes without error, but no data is loaded.
??
SELECT company, type, name, address1, address2, address3, address4, contact, phone, fob, freightterms, shipvia, paymentterms, customernum, co1, co2,
co3, co4, co5, co6, co7, co8, co9, co10, ldkey, fax, co11, co12, co13, changeby, changedate, inclusive1, inclusive2, inclusive3, tax1code, tax2code,
tax3code, currencycode, location, registration1, registration2, registration3, apcontrolacc, apsuspenseacc, rbniacc, payvendor, bankaccount,
inclusive4, inclusive5, registration4, registration5, tax4code, tax5code, disabled, remitaddress1, remitaddress2, remitaddress3, remitaddress4,
remitcontact, payonreceipt, homepage, banknum, dunsnum, taxexemptcode, taxexemptnum, ecommerceenabled, mnetcompanyid, co14, co15, co16,
co17, co18, sourcesysid, ownersysid, externalrefid, apiseq, interid, migchangeid, sendersysid, autoreceiveonasn, vendorsendsasn, vendorsendsinv,
ecominterface, mnetecomnum1, mnetecomnum2, mnetecomnum3, mnetecomnum4, vendorsendsstatus, autoapproveinv, autosendpocancel, orgid,
defaultwarehouse, parentcompany, useparentremitto, address5, remitaddress5, catalogname, punchoutenabled
FROM DTS_companies
WHERE (NOT EXISTS
(SELECT *
FROM MAXSYR.dbo.companies))
-
Sorry I didn't elaborate. Your query needs to check for the existence of a match between the two tables. The WHERE clause should look like this:
FROM DTS.companies d
WHERE NOT EXISTS (SELECT * FROM MAXSYR.dbo.companies m
WHERE m.company = d.company
and m.type = d.type
and m.name = d.name)
**use whatever columns make up the primary key**
Greg
Greg
-
Also, for efficiencies sake, you should specify a column in the SELECT statement.
EG:
WHERE NOT EXISTS (SELECT [your id fields] FROM MAXSYR etc...)
This will prevent the query from spooling up the entire record just to check for existence.
--------------------
Colt 45 - the original point and click interface
-
Thanks for all your help. I've written several packages using this statement. Just what I needed.
Regards,
Joan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply