[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Why dosent this work ?

Hi!

Have this query:

INSERT INTO [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] (LINENUM,ITEMID, 
      SALESPRICE,SALESUNIT,SALESQTY,LINEAMOUNT,CUSTACCOUNT,CUSTOMERREF,SALESID,PURCHORDERFORMNUM,CREATEDDATETIME,DATAAREAID,RECID)

SELECT dbo.HAU_Salesline.LINENUM,dbo.HAU_Salesline.ITEMID, 
dbo.HAU_Salesline.SALESPRICE,dbo.HAU_Salesline.SALESUNIT,
dbo.HAU_Salesline.SALESQTY,dbo.HAU_Salesline.LINEAMOUNT,
dbo.HAU_Salesline.CUSTACCOUNT,dbo.HAU_Salesline.CUSTOMERREF,
dbo.HAU_Salesline.SALESID,dbo.HAU_Salesline.PURCHORDERFORMNUM,
dbo.HAU_Salesline.CREATEDDATETIME,dbo.HAU_Salesline.DATAAREAID,
dbo.HAU_Salesline.LINENUM

FROM dbo.HAU_Salesline

Open in new window


Get this error message:
[Error Code: 2601, SQL State: 23000]  Cannot insert duplicate key row in object 'dbo.HAU_SALESLINE' with unique index 'I_40113RECID'. The duplicate key value is (NOT2, 1).

Here is the table  dbo.HAU_Salesline

CREATE TABLE
    HAU_SalesLine
    (
        SALESID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINENUM NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ITEMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESSTATUS INT DEFAULT 0 NOT NULL,
        LEDGERACCOUNT NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        NAME NVARCHAR(1000) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        EXTERNALITEMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        QTYORDERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESDELIVERNOW NUMERIC(28,12) DEFAULT 0 NOT NULL,
        REMAINSALESPHYSICAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        REMAINSALESFINANCIAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        COSTPRICE NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESPRICE NUMERIC(28,12) DEFAULT 0 NOT NULL,
        CURRENCYCODE NVARCHAR(3) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINEPERCENT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        LINEDISC NUMERIC(28,12) DEFAULT 0 NOT NULL,
        LINEAMOUNT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        CONFIRMEDDLV DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RESERVATION INT DEFAULT 0 NOT NULL,
        SALESGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESUNIT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION2_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION3_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION4_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION5_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION6_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PRICEUNIT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        INVENTTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        CUSTGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        CUSTACCOUNT NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESMARKUP NUMERIC(28,12) DEFAULT 0 NOT NULL,
        INVENTDELIVERNOW NUMERIC(28,12) DEFAULT 0 NOT NULL,
        MULTILNDISC NUMERIC(28,12) DEFAULT 0 NOT NULL,
        MULTILNPERCENT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESTYPE INT DEFAULT 0 NOT NULL,
        BLOCKED INT DEFAULT 0 NOT NULL,
        COMPLETE INT DEFAULT 0 NOT NULL,
        REMAININVENTPHYSICAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        TRANSACTIONCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXITEMGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXAUTOGENERATED INT DEFAULT 0 NOT NULL,
        UNDERDELIVERYPCT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        OVERDELIVERYPCT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        BARCODE NVARCHAR(80) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        BARCODETYPE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTREFTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTREFTYPE INT DEFAULT 0 NOT NULL,
        INVENTREFID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ITEMBOMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINEHEADER NVARCHAR(80) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SCRAP INT DEFAULT 0 NOT NULL,
        DLVMODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTTRANSIDRETURN NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTDIMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TRANSPORT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        STATPROCID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PORT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        RECEIPTDATEREQUESTED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        CUSTOMERLINENUM INT DEFAULT 0 NOT NULL,
        PACKINGUNITQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        PACKINGUNIT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        REMAININVENTFINANCIAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        DELIVERYADDRESS NVARCHAR(250) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYNAME NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYSTREET NVARCHAR(250) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYZIPCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCITY NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCOUNTY NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYSTATE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCOUNTRYREGIONID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYTYPE INT DEFAULT 0 NOT NULL,
        CUSTOMERREF NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PURCHORDERFORMNUM NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        RECEIPTDATECONFIRMED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        BLANKETREFTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        STATTRIANGULARDEAL INT DEFAULT 0 NOT NULL,
        SHIPPINGDATEREQUESTED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        SHIPPINGDATECONFIRMED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        ADDRESSREFRECID BIGINT DEFAULT 0 NOT NULL,
        ADDRESSREFTABLEID INT DEFAULT 0 NOT NULL,
        EINVOICEACCOUNTCODE NVARCHAR(35) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERACCOUNT NVARCHAR(25) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERDLVTYPE INT DEFAULT 0 NOT NULL,
        SHIPCARRIERACCOUNTCODE NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYDATECONTROLTYPE INT DEFAULT 0 NOT NULL,
        ATPINCLPLANNEDORDERS INT DEFAULT 0 NOT NULL,
        ATPTIMEFENCE INT DEFAULT 0 NOT NULL,
        RETURNALLOWRESERVATION INT DEFAULT 0 NOT NULL,
        ITEMREPLACED INT DEFAULT 0 NOT NULL,
        RETURNDEADLINE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        EXPECTEDRETQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        RETURNSTATUS INT DEFAULT 0 NOT NULL,
        RETURNARRIVALDATE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RETURNCLOSEDDATE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RETURNDISPOSITIONCODEID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        EDIORIGORDEREDQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPALLETDISCQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCDISCPCT2 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCDISCPCT1 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        EMPTYGOODTRANSPORTERID NVARCHAR(30) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        CREATEDDATETIME DATETIME DEFAULT DATEADD(millisecond, -DATEPART(millisecond,GETUTCDATE()),
        GETUTCDATE()) NOT NULL,
        DATAAREAID NVARCHAR(4) COLLATE Danish_Norwegian_CI_AS DEFAULT 'dat' NOT NULL,
        RECVERSION INT DEFAULT 1 NOT NULL,
        RECID BIGINT NOT NULL,
        ABCDISCPCT3 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPALLETLAYERDISCQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABC_REASONCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABCPCEORDERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPCEDELIVERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCUNITID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABC_CONFIRMEDDLV DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        PROJTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJCATEGORYID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJLINEPROPERTYID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ACTIVITYNUMBER NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABC_ALLOWZEROPRICE INT DEFAULT 0 NOT NULL,
        ABCPANTEVARE INT DEFAULT 0 NOT NULL,
        PRIMARY KEY (SALESID, LINENUM, DATAAREAID, RECID)
    )

Open in new window


And here is the table from linked server:

CREATE TABLE
    HAU_SALESLINE
    (
        SALESID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINENUM NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ITEMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESSTATUS INT DEFAULT 0 NOT NULL,
        LEDGERACCOUNT NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        NAME NVARCHAR(1000) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        EXTERNALITEMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        QTYORDERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESDELIVERNOW NUMERIC(28,12) DEFAULT 0 NOT NULL,
        REMAINSALESPHYSICAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        REMAINSALESFINANCIAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        COSTPRICE NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESPRICE NUMERIC(28,12) DEFAULT 0 NOT NULL,
        CURRENCYCODE NVARCHAR(3) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINEPERCENT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        LINEDISC NUMERIC(28,12) DEFAULT 0 NOT NULL,
        LINEAMOUNT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        CONFIRMEDDLV DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RESERVATION INT DEFAULT 0 NOT NULL,
        SALESGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESUNIT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION2_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION3_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION4_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION5_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DIMENSION6_ NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PRICEUNIT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        PROJTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        CUSTGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        CUSTACCOUNT NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SALESQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESMARKUP NUMERIC(28,12) DEFAULT 0 NOT NULL,
        INVENTDELIVERNOW NUMERIC(28,12) DEFAULT 0 NOT NULL,
        MULTILNDISC NUMERIC(28,12) DEFAULT 0 NOT NULL,
        MULTILNPERCENT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        SALESTYPE INT DEFAULT 0 NOT NULL,
        BLOCKED INT DEFAULT 0 NOT NULL,
        COMPLETE INT DEFAULT 0 NOT NULL,
        REMAININVENTPHYSICAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        TRANSACTIONCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXITEMGROUP NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TAXAUTOGENERATED INT DEFAULT 0 NOT NULL,
        UNDERDELIVERYPCT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        OVERDELIVERYPCT NUMERIC(28,12) DEFAULT 0 NOT NULL,
        BARCODE NVARCHAR(80) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        BARCODETYPE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTREFTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTREFTYPE INT DEFAULT 0 NOT NULL,
        INVENTREFID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ITEMBOMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        LINEHEADER NVARCHAR(80) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SCRAP INT DEFAULT 0 NOT NULL,
        DLVMODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTTRANSIDRETURN NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJCATEGORYID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        INVENTDIMID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        TRANSPORT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        STATPROCID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PORT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PROJLINEPROPERTYID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        RECEIPTDATEREQUESTED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        CUSTOMERLINENUM INT DEFAULT 0 NOT NULL,
        PACKINGUNITQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        PACKINGUNIT NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        REMAININVENTFINANCIAL NUMERIC(28,12) DEFAULT 0 NOT NULL,
        DELIVERYADDRESS NVARCHAR(250) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYNAME NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYSTREET NVARCHAR(250) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYZIPCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCITY NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCOUNTY NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYSTATE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYCOUNTRYREGIONID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYTYPE INT DEFAULT 0 NOT NULL,
        CUSTOMERREF NVARCHAR(60) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        PURCHORDERFORMNUM NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        RECEIPTDATECONFIRMED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        BLANKETREFTRANSID NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        STATTRIANGULARDEAL INT DEFAULT 0 NOT NULL,
        SHIPPINGDATEREQUESTED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        SHIPPINGDATECONFIRMED DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        ADDRESSREFRECID BIGINT DEFAULT 0 NOT NULL,
        ADDRESSREFTABLEID INT DEFAULT 0 NOT NULL,
        EINVOICEACCOUNTCODE NVARCHAR(35) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERACCOUNT NVARCHAR(25) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        SHIPCARRIERDLVTYPE INT DEFAULT 0 NOT NULL,
        SHIPCARRIERACCOUNTCODE NVARCHAR(20) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        DELIVERYDATECONTROLTYPE INT DEFAULT 0 NOT NULL,
        ATPINCLPLANNEDORDERS INT DEFAULT 0 NOT NULL,
        ATPTIMEFENCE INT DEFAULT 0 NOT NULL,
        ACTIVITYNUMBER NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        RETURNALLOWRESERVATION INT DEFAULT 0 NOT NULL,
        ITEMREPLACED INT DEFAULT 0 NOT NULL,
        RETURNDEADLINE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        EXPECTEDRETQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        RETURNSTATUS INT DEFAULT 0 NOT NULL,
        RETURNARRIVALDATE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RETURNCLOSEDDATE DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        RETURNDISPOSITIONCODEID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        EDIORIGORDEREDQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPCEORDERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPCEDELIVERED NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCUNITID NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABCPALLETDISCQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCDISCPCT2 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCDISCPCT1 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        EMPTYGOODTRANSPORTERID NVARCHAR(30) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABCDISCPCT3 NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABCPALLETLAYERDISCQTY NUMERIC(28,12) DEFAULT 0 NOT NULL,
        ABC_REASONCODE NVARCHAR(10) COLLATE Danish_Norwegian_CI_AS DEFAULT '' NOT NULL,
        ABC_CONFIRMEDDLV DATETIME DEFAULT '1900-01-01 00:00:00.000' NOT NULL,
        ABC_ALLOWZEROPRICE INT DEFAULT 0 NOT NULL,
        ABCPANTEVARE INT DEFAULT 0 NOT NULL,
        CREATEDDATETIME DATETIME DEFAULT DATEADD(millisecond, -DATEPART(millisecond,GETUTCDATE()),
        GETUTCDATE()) NOT NULL,
        DATAAREAID NVARCHAR(4) COLLATE Danish_Norwegian_CI_AS DEFAULT 'dat' NOT NULL,
        RECVERSION INT DEFAULT 1 NOT NULL,
        RECID BIGINT NOT NULL,
        CONSTRAINT I_40113SALESLINEIDX PRIMARY KEY (DATAAREAID, SALESID, LINENUM, RECID),
        CHECK ([RECID]<>(0))
    )

Open in new window



What is the problem here ?
0
team2005
Asked:
team2005
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error tells you that you are trying to generate a duplicate value  ...

so, presumably, you need to add a NOT EXISTS to the SELECT, and even eventually make sure that the rows you are insertin are not presending the duplicate data as such.

in short, this might be something to start with:

INSERT INTO [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] (LINENUM,ITEMID, 
      SALESPRICE,SALESUNIT,SALESQTY,LINEAMOUNT,CUSTACCOUNT,CUSTOMERREF,SALESID,PURCHORDERFORMNUM,CREATEDDATETIME,DATAAREAID,RECID)

SELECT sl.LINENUM, sl.ITEMID, 
sl.SALESPRICE,sl.SALESUNIT,
sl.SALESQTY,sl.LINEAMOUNT,
sl.CUSTACCOUNT,sl.CUSTOMERREF,
sl.SALESID,sl.PURCHORDERFORMNUM,
sl.CREATEDDATETIME, slDATAAREAID,
sl.LINENUM

FROM dbo.HAU_Salesline sl
WHERE NOT EXISTS(
    SELECT NULL FROM [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] d
   WHERE d.LINENUM = sl.LINENUM
     AND d.ITEMID = sl.ITEMID
   )

Open in new window

presuming that the 2 field itemit + linenum is what forms the primary key
0
 
team2005Author Commented:
Hi!

Dosent help ?

Give me this error message :
[Error Code: 2601, SQL State: 23000]  Cannot insert duplicate key row in object 'dbo.HAU_SALESLINE' with unique index 'I_40113RECID'. The duplicate key value is (NOT2, 2).
0
 
Pratima PharandeCommented:
try this




INSERT INTO [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] (LINENUM,ITEMID,
      SALESPRICE,SALESUNIT,SALESQTY,LINEAMOUNT,CUSTACCOUNT,CUSTOMERREF,SALESID,PURCHORDERFORMNUM,CREATEDDATETIME,DATAAREAID,RECID)

SELECT sl.LINENUM, sl.ITEMID,
sl.SALESPRICE,sl.SALESUNIT,
sl.SALESQTY,sl.LINEAMOUNT,
sl.CUSTACCOUNT,sl.CUSTOMERREF,
sl.SALESID,sl.PURCHORDERFORMNUM,
sl.CREATEDDATETIME, sl.DATAAREAID,
sl.LINENUM

FROM dbo.HAU_Salesline sl
WHERE NOT EXISTS(
    SELECT NULL FROM [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] d
   WHERE d.LINENUM = sl.LINENUM
     AND d.ITEMID = sl.ITEMID
      AND d.SALESID = sl.SALESID
AND d.DATAAREAID = sl.DATAAREAID
   )
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
team2005Author Commented:
Hi!

Dosent help ?
0
 
Jared_SCommented:
Everyone is right about the error.  Are [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE] and  dbo.HAU_Salesline correct for the insert into/select from table names?

The primary key

PRIMARY KEY (SALESID, LINENUM, DATAAREAID, RECID)

for one or more of your inserted rows already exists.

There are queries shown above that should limit the insert to non-duplicates.

If duplicates aren't ever expected, you can use this to find the matching records.

SELECT dbo.HAU_Salesline.LINENUM,
dbo.HAU_Salesline.SALESID, dbo.HAU_Salesline.DATAAREAID,
dbo.HAU_Salesline.LINENUM
FROM dbo.HAU_Salesline

INTERSECT 

select LINENUM, SALESID, DATAAREAID, RECID 
from [ssg-sql01].[ssg-axdb5-utvikling].[DBO].[HAU_SALESLINE]

Open in new window

0
 
team2005Author Commented:
Hi!

First of all:
 [ssg-sql01].[ssg-axdb5-utvikling] is a linked server.
That are located on onother server.

I have no duplicates in the database.

Tryed to create a duplicate of table >dbo.HAU_Salesline
and try to run the query to the new table.
And that works fine ?

The problem must be somthing to do with the link to the other server ?
0
 
Jared_SCommented:
Did the index get created on your copied table?
0
 
team2005Author Commented:
Yes
0
 
Jared_SCommented:
The only other thing I could think of to try would be to rebuild the indexes on the tables.

http://technet.microsoft.com/en-us/library/ms189858.aspx

It's simple to do.
0
 
team2005Author Commented:
thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now