Thanks in advance for any help. I am trying to insert into a sql table using data from an oracle server using a linked server. I have noticed it ignores any lines which are identical. However I do not want to ignore these and would like the query to insert the rows. I have tried using an identity field to make the rows unique but this has not provided the desired results.
Has anyone come across this before. My query is as below I have included a section of the key part of a stored proc:-
SELECT @TSQL =
'INSERT INTO [VM].Share.dbo.tbl_PSA_LIVE
SELECT * FROM OPENQUERY(LLIVE1, ''
SUBSTR(PAYRUN.TAX_YEAR, 1,4) AS TAX_YEAR,
PAYRUN.TAX_PERIOD AS TAX_PERIOD,
SUBSTR(PAYRUN.EMPLOYEE_NUMBER, 1,6) AS EMPLOYEE_NUMBER,
PAYRUN.TITLE || CHR(32) ||PAYRUN.INITIALS || CHR(32) || PAYRUN.SURNAME AS FULLNAME,
SUBSTR(PAYRUN.TAX_CODE,1,9) AS TAX_CODE,
SUBSTR(PAYRUN.NI_NO,1,10) AS NI_NO,
SUBSTR(PAYRUN.PAY_METHOD, 1,10) AS PAY_METHOD,
SUBSTR(PAYRUN.PAY_TYPE,1,1) AS PAY_TYPE,
PAYG.ID AS PAYGID,
CURRENT_TIMESTAMP AS EXTRACT_DATE FROM D562M PAYRUN
INNER JOIN D100M PAYG ON PAYG.REF = PAYRUN.PAY_STR_REF
WHERE PAYRUN.TAX_YEAR = ''''' + @Year + '''''
AND PAYRUN.TAX_PERIOD =''''' + @TPeriod + '''''
AND SUBSTR(PAYG.ID,1,2) IN (''''' + @P_TypeMB + ''''')
Any other rows of data seem to be fine being inserted just the identical lines which seem to be being ignored. So if I have two rows the same only one will be inserted.
- This topic was modified 6 months, 2 weeks ago by Johnny D.