OPENQUERY is not inserting identical rows

  • Johnny D

    Ten Centuries

    Points: 1030

    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

    • This topic was modified 6 months, 2 weeks ago by  Johnny D.
  • Erland Sommarskog

    SSC-Insane

    Points: 23868

    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]

  • Johnny D

    Ten Centuries

    Points: 1030

    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.

  • Erland Sommarskog

    SSC-Insane

    Points: 23868

    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]

  • Johnny D

    Ten Centuries

    Points: 1030

    That inserts three rows. Which leaves me confused even more.

  • Erland Sommarskog

    SSC-Insane

    Points: 23868

    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]

  • Johnny D

    Ten Centuries

    Points: 1030

    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. 🙂

  • Erland Sommarskog

    SSC-Insane

    Points: 23868

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply