sql - LEFT OUTER JOIN causing violation of unique key constraint -

-

I have two databases for two companies running the same software, thus DB structure is similar.

Windows Server 2003, MS SQL Saver 2005

I am trying to establish a list of items and copy the associated tables from the SERSEEET.

There are a total of 11 tables, I am using the same format for the script which is 11 for everyone to work. Three of them are failing

The example below is one of three: 

  - dbo.ITEM_MEASURE 5 SET IDENTITY_INSERT DESTINATION.dbo. ITEM_MEASURE at INSERT DESTINATION.dbo. Select ITEM_MEASURE (ITEM_MEASURE_ID, MEAS_TYPE, ITEMNO, MEAS_CODE, SELLPRIC, MARKUP, S_PERC_DOC, MIN_AMOUNT, COSTPERSP, COST, COST_LOW, COST_HIGH, WEIGHT_MEAS, WEIGHT, SIZE_MEAS, height, width, height, VOLUME_MEAS, section, LAST_COST) s_ITEM_MEASURE_ID, S.MEAS_TYPE, s.ITEMNO, s.MEAS_CODE, s.SELLPRIC, s.MARKUP, s.S_PERC_DOC, s.MIN_AMOUNT, s.COSTPERSP, '0', '0', '0', s.WEIGHT_MEAS, s WE Ight, s.SIZE_MEAS, s.LENGTH, s.BREADTH, s.HEIGHT, s.VOLUME_MEAS, s.VOLUME, '0' SOURCE.dbo.ITEM_MEASURE Join Left Exterior but DESTINATION.dbo.ITEM_MEASURE d from (d) .ITEM_MEASURE_ID = s.ITEM_MEASURE_ID) Where d.ITEM_MEASURE_ID is zero set identity_insert DESTINATION.dbo.ITEM_MEASURE closed / * error message 2627, level 14, state 1, 73 violation of line unique key condition 'IN_ITEM_MEAS URE_UQ_ITEM_TYPE_MEAS '. The item 'dbo.ITEM_MEASURE' can not contain duplicate keys. The statement has been terminated. * /

The table P is ITEM_MEASURE_ID, there are either a duplicate in the source or destination are separated and as I understand it "where d.ITEM_MEASURE_ID is zero" statement copy it Attempted data from which the DESTINATION already exists.

Why did I get this error?

Update - Can not post pictures, but here's a shortage and indexed.: A look at

is updated - Properties at IN_ITEM_MEASURE_UQ_ITEM_TYPE_MEASURE:

UNIQUE constraints (div)

< P> or unique index) In addition to their PK, for example, the name of the infringement obligation states that it can be an obstacle such as 

  UNIQUE (ITEMNO, MEAS_TYPE)

... the pillars There is a unique index on such a collection of that in two unrelated databases, the equivalent (ITEMNO, MEAS_TYPE) pairs (or any other non-P's data) will be connected to the same PKS, so to avoid violation of your P Assuming the strategy is a special reason to avoid the violation of such an obstacle, such as nothing to avoid.

For that matter, you have not believed for any reason that PK is correlated in any way in its source and destination tables. Although this copy of the table can appear without any error, I have no reason to believe that the query that you present is actually doing the right thing.

Edit to add : In fact, it looks like ITEM_MEASURE_ID is a surrogate key (i.e. inventions from any one or dbms, any Data-independent) It is extremely unlikely that source data matching destination data independently entered by such key will give you meaningful results (not in other tables, either) Instead you should use a natural key, such as (ITEMNO, MEAS_TYPE) if it is actually a suit where there are UNIQUE barriers or unique indexes on your code, they Can not serve as a clue for natural keys, insert values ​​for DESTINATION.dbo. ITEM_MEASURE (MEAS_TYPE, ITEMNO, MEAS_CODE, SELLPRIC, markup: - Note 

  can not serve as a clue for natural keys: That's what this might look like instead of this table for isolation, S_PERC_DOC, MIN _AMOUNT, COSTPERSP, COST, COST_LOW, COST_HIGH, WEIGHT_MEAS, weight, SIZE_MEAS, LENGTH, BREADTH, HEIGHT, VOLUME_MEAS, VOLUME, LAST_COST) SELECT s.MEAS_TYPE, s. ITEMNO, s.MEAS_CODE, s.SELLPRIC, markup, s.S_PERC_DOC, s.MIN_AMOUNT, s.COSTPERSP, '0', '0', '0', s.WEIGHT_MEAS, s.WEIGHT, s.SIZE_MEAS, s .LENGTH, s.BREADTH, s.HEIGHT, s.VOLUME_MEAS, s.VOLUME, '0' SOURCE.dbo.ITEM_MEASURE from left exterior to DESTINATION.dbo.ITEM_MEASURE d include (d.ITEMNO = s.ITEMNO) and (D.MEAS_TYPE = s.MEAS_TYPE) ou d. ITEMNO is zero

There is a lot of messier that you need to deal with related source tables because PKS source tables in destination tables differ from PKS, but it can be done is.


Comments

Post a Comment

Popular posts from this blog

HTML/CSS - Automatically set height width from background image? -

-
Long time ... I do not think it's possible, but I'm shocked before! I tag anchor tags, all of which are background images, all 300px wide but their height is all the difference, is it really set to do without typing altitude? Is it to set the dimensions of BG URL? Thank you! I do not think people understand - my mistake is to hurry the question. Here is the code as an example: # ago -1 {width: 300px; Height: 410px; Background: url ('/ image-1.jpg');} # East -2 {width: 300px; Height: 420px; Background: url ('/ image-2.jpg');} # Pre-3 {width: 300px; Height: 430px; Background: url ('/ image-3.jpg');} # Pre-4 {width: 300px; Height: 440px; Background: url ('/ image-3.jpg');} & lt; A href = "#" id = "ex-1" & gt; & Lt; / A & gt; & Lt; A href = "#" id = "ex-2" & gt; & Lt; / A & gt; & Lt; A href = "#" id = "pre-3" & gt; & Lt; / A & gt...
Read more

php - Mysql Show Process - Sleep Commands and what to do -

-
I am thinking that there is something that I can do better so that my process list should start with the command with MySQL Be clear Currently I am looking for a lot: 17325 user_a localhost db_1 sleep 1132 NULL 17464 user_a localhost db_1 sleep 1124 NULL 17983 user_a localhost db_1 sleep 1078 null 18113 user_a localhost db_1 sleep 1068 NULL 18207 user_a localhost db_1 sleep 1060 NULL 18231 user_a localhost db_1 sleep 1058 NULL 18353 user_a localhost db_1 sleep 1047 NULL 18447 user_a localhost db_1 sleep 1040 null 18489 user_a localhost db_1 sleep 1036 NULL 23408 user_a localhost db_1 sleep 637 NULL Something in my phpscript is that I want to clean it Can i The script looks like: Select the ID from $ q = 'db_1 where fkId = 2'; $ Ar = mysql_query ($ q); ($ Line = mysql_fetch_array ($ qr)) {echo ($ row ['id'];} Thanks in advance! I'm guessing that when you have 10 threads, you're constantly using the connectio...
Read more

c - What is the address of buf (the local variable in the main function)? -

-
I had exercise with programming in c or classes and I followed the question: Is Buf (local variable in main function)? Either enter the answer in hexadecimal format (like 0x 8 "digits" 0-9 or A-F, 0xbfff0014) or in decimal format. Note here that we want the address of the buff, not its content. The code looks like this: int main (int argc, char * argv []) {while (1) {four buff [1024] = {0}; Int r; .... I then started the code and put the built code in the buff [1024] line and in Typed Gdb p & amp; Buf and it gives me results: 0xbffff0f0 But when I use it in this quiz it gives me the result that this value is wrong. My question is this: Is this the name of the Buff variable (P & E)? Or if not, why not? Exercise is done on the virtualbox machine, so I think everyone should be the same address There is no correct numerical answer, the address may vary from one time to another, it is a programming drill that you say the following three...
Read more