-
openquery in 2000/2005
Have two tables that I want to merge and insert the data into one table.
How do I join two tables from AS400 using openquery and join them on LoanId.
Currently I'm inserting into two tables then merge them. My only problem is that
The way I've joined the tables using a select runs for a long time
because table DTA.Q00022 has a lot of records. Whats the best way to do this
TRUNCATE TABLE EDW_STAGE..CM_STAGE_LOANQ00022
INSERT EDW_STAGE..CM_STAGE_LOANQ00022
SELECT
CONVERT(INT,LoanId) AS LoanId
,CAST(CONVERT(VARCHAR(30),AccountNumber) AS BIGINT) AS AccountNo
,CONVERT(VARCHAR(30),CardNumber) AS CardNumber
FROM
OPENQUERY(AS400,'SELECT * FROM DTA.Q00022')
GO
--- My attempt
SELECT
CONVERT(INT,LoanId ) AS LoanId
,CONVERT(VARCHAR(10),LoanOfficerCode) AS LoanOfficerCode
,CONVERT(VARCHAR(20),IDNumber ) AS IDNumber
FROM
OPENQUERY(AS400,'SELECT * FROM ZALIVDTA.LOANQ00001')
WHERE LoanId IN (SELECT LoanId FROM OPENQUERY(AS400,'SELECT * FROM DTA.Q00022'))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|