Problem with Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Insert

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 13:47:55

Have the following insert statement. The orderrebatehistory table has 22 records in it. When the script is ran it is only bringing over 21 records.

The Ext_rebate is the same for two records. If I change the amt on one of those records all 22 records come over.

use [data_02]
begin
insert into gbkmut(bkjrcode,
reknr,
datum,
periode,
bkstnr,
dagbknr,
oms25,
bdr_hfl,
btw_code,....
.......
select distinct
orderrebatehistory.bkjrcode,
orderrebatehistory.reknr,
gbkmut.datum,
gbkmut.periode,
gbkmut.bkstnr,
gbkmut.dagbknr,
gbkmut.oms25,
orderrebatehistory.ext_rebate,
gbkmut.btw_code,.....
.......
FROM orderrebatehistory
INNER JOIN gbkmut ON
orderrebatehistory.ord_no = gbkmut.bkstnr_sub and
orderrebatehistory.Inv_no = gbkmut.faktuurnr
where OrderRebateHistory.Posted_fg = 'N' and gbkmut.reknr =' 12001'
end

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-18 : 13:57:43

That's because you are selecting distinct records. If you
want all records, don't use the distinct keyword.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 13:58:29

I think the where condition may the reason. Or that record wont be existing on gbkmut with same values of bkstnr_sub & faktuurnr fields as in orderrebatehistory table.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 13:58:53

When I don't use distinct I writes 44 records instead of 22 records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 13:59:36

You are only getting 21 records because there are only 21 records that match your join criteria and/or your where clause.

For the record that doesn't get inserted, show us the row in both tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:00:25

All records in the OrderRebateHistory have the same Order_no and inv_no. the 22 records are all on the same order.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:02:21

To tkizer:

Did you miss where I mentioned that if I change the ext_rebate on one of the records all 22 records come over. Two records had an ext_rebate of .07, I changed one to .08 and all 22 came over.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 14:05:57

Please show us the data for that row. Provide the row in both tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:07:37

Question:
So with a select Distinct it will only look at rows that have every field that is different??

Out of the 21 rows that did come across, there are over 10 fields that all have the same value.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:09:00

I don't know an easy way to get the data into the window. Does not stay lined up.

I can email a spreadsheet.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 14:15:43

Use code tags to retain your formatting when posting data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:22:42

These are the two records in the orderrebatehistory. If I change one of them where it says .07 to .08 both come in. As long as they both say .07 only one comes over.
ordno item

O 609792 471750 21 9 1.49 0.074 0.07 5 1 1 W240 PR160 3 359667 66946 Y
O 609792 224026 19 9 1.38 0.069 0.07 5 1 1 W240 PR160 3 359667 66944 Y

this is a snip it of the gbkmut
reknr bkstnr_sub faktunnr
12001 609792 359667

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:22:42

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 14:32:20

quote:
Originally posted by Vack

These are the two records in the orderrebatehistory. If I change one of them where it says .07 to .08 both come in. As long as they both say .07 only one comes over.
ordno item

O 609792 471750 21 9 1.49 0.074 0.07 5 1 1 W240 PR160 3 359667 66946 Y
O 609792 224026 19 9 1.38 0.069 0.07 5 1 1 W240 PR160 3 359667 66944 Y

this is a snip it of the gbkmut
reknr bkstnr_sub faktunnr
12001 609792 359667

How is Posted_fg value determined? Is it some kind of calaculated field? you're using this filter condition

OrderRebateHistory.Posted_fg = 'N'
and i cant spot any field in posted records having this value (i can see 1 with 'Y' though)

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:37:43

Those records are after the script ran. It marks all records as posted even though one of them did not make it.

there is a trigger on the gbkmut that after the insert to go back and update the orderrebatehistory posted_fg with Y.

When records are written to the orderrebatehistory posted_fg comes in as N

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 14:42:02

have an update here.
If I do take the Distinct out all 22 records will come over, but that messes up another calculation I have going on with the GBkmut after the insert of records. Without the select distinct the calc below subtracts to much.

begin
UPDATE e
SET e.bdr_hfl = e.bdr_hfl - d.x
FROM gbkmut AS e
INNER JOIN (
SELECT faktuurnr,
SUM(bdr_hfl) AS x
FROM inserted
WHERE freefield3 = 'Rebate'
GROUP BY faktuurnr
) AS d ON d.faktuurnr = e.faktuurnr
WHERE e.reknr = ' 12001'
end

So I'm not sure which is easier to fix. Leave the distinct in and try and figure out why one record is not coming across or take the distinct out and figure out why my calculation is off.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 14:59:01

quote:
Originally posted by Vack

have an update here.
If I do take the Distinct out all 22 records will come over, but that messes up another calculation I have going on with the GBkmut after the insert of records. Without the select distinct the calc below subtracts to much.

begin
UPDATE e
SET e.bdr_hfl = e.bdr_hfl - d.x
FROM gbkmut AS e
INNER JOIN (
SELECT faktuurnr,
SUM(bdr_hfl) AS x
FROM inserted
WHERE freefield3 = 'Rebate'
GROUP BY faktuurnr
) AS d ON d.faktuurnr = e.faktuurnr
WHERE e.reknr = ' 12001'
end

So I'm not sure which is easier to fix. Leave the distinct in and try and figure out why one record is not coming across or take the distinct out and figure out why my calculation is off.

Then chance is that all field values that you specify in distinct list might be same for these two records so that only one comes out.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-06-18 : 15:29:03

I added a new field to the select statement that was unique and found a place to insert that into the gbkmut. That fixed my issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 02:37:31

quote:
Originally posted by Vack

I added a new field to the select statement that was unique and found a place to insert that into the gbkmut. That fixed my issue.

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources