November 14, 2019 at 10:24 am
Hi,
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, ''
SELECT
PAYRUN.PERSON_REF,
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,
PAYRUN.PAY_DATE,
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.
Thanks
November 14, 2019 at 11:59 am
What happens if you run the SELECT on its own? What happens if you insert into a local table?
And I can't escape wondering how come you are not running this on the instance where the target table resides. Using an SQL Server instance to get data from one server to pass to another seems like a waste of resources. Maybe SSIS also could be an alternative?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 14, 2019 at 1:11 pm
Thanks Erland. The select runs fine and selects the necessary data. Insert to local table gives the same result. Identical duplicates are omitted. The reasons you ask 'why' are historic and the way the IT department have allowed us to use the servers.
November 14, 2019 at 2:07 pm
If you run
CREATE TABLE #temp(a int NOT NULL)
INSERT #temp(a)
SELECT a FROM OPENQUERY(LLIVE1, 'SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual')
how many rows are inserted into the temp table?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 14, 2019 at 4:19 pm
That inserts three rows. Which leaves me confused even more.
November 14, 2019 at 4:50 pm
I'm starting to suspect that the issue is with something you have not told us about. It would not be the first that this happens in a forum thread....
But you can try the below. I've added a row_number column to the Oracle query, so that the rows returned are unique. If your duplicates still are removed, try also with a temp table - and in the latter case, you could insert the rowno column too.
SELECT @TSQL =
'INSERT INTO [VM].Share.dbo.tbl_PSA_LIVE
SELECT PERSON_REF, TAX_YEAR, TAX_PERIOD, EMPLOYEE_NUMBER,
FULLNAME, TAX_CODE, NI_NO, PAY_METHOD, PAY_TYPE, PAY_DATE,
PAYGID, EXTRACT_DATE
FROM OPENQUERY(LLIVE1, ''
SELECT PAYRUN.PERSON_REF,
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,
PAYRUN.PAY_DATE,
PAYG.ID AS PAYGID,
CURRENT_TIMESTAMP AS EXTRACT_DATE,
row_number() OVER (ORDER BY PAYRUN.PERSON_REF, PAYG.ID) AS rowno
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 + ''''')
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 15, 2019 at 8:16 am
Hi Erland, Thanks for your help. I spotted the UNION ALL in your query and tried again with that instead and it worked. I didn't think to try that earlier as I thought the query would only eliminate duplicates between two different queries that form a UNION not if they were in one script. Thanks for your help as it showed me the way. Just can't believe it was something this simple. I guess every days a school day. 🙂
November 15, 2019 at 9:06 am
As I said, the problem was in something you did not tell us about. There was no UNION in what you posted, was there? 🙂
But that is correct, UNION implies DISTINCT, and it is across the queries as well as within. So if a query produces duplicates and you have a UNION with another query, you will lose the dups in the first query, even if the queries are entirely disjunct with each other. This applies, no matter you are on SQL Server or Oracle or something else.
Generally, I have found after having written SQL code for many years, that UNION ALL is the normal thing and what you want in most cases. So I would recommend to make it a habit to use UNION ALL unless you know that you really want to eliminate duplicates. Since dups-elimination is costly, UNION ALL is also more efficient.
I only used UNION ALL, because it was the easiest way to produce a result set with duplicates. I could also have written
FROM (VALUES(1), (1), (1)) AS V(v)
But since I don't use Oracle myself, I don't know if that syntax works on Oracle.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply