Insert Question - 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

General SQL Server Forums

New to SQL Server Programming

Insert Question

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 12:05:54

ope this makes my insert issue more clear.
I want to insert one record into an order detail table based off of records existing in an orderrebate table.


OEORDLIN_SQL will look something like this:
ord_type   ord_no  line_seq_no   item_no   unit_price
 O         2343        1          PartA      3.00
 O         2343        2          PartB      3.40
 O         2343        3          PartC      5.00

THE ORDERREBATE WILL LOOK LIKE THIS:
ord_type   ord_no   line_seq_no   item_no   ext_rebate
 O         2343         1          PartA      .50
 O         2342         3          PartC      .25

What I want to do is insert 1 record into the oeordlin for each order that is in the orderrebate to look like this:
ord_type   ord_no   line_seq_no   item_no   unit_price
  O         2343        4         REBATE       .75

So when the script finishes my oeordlin will look like this:
ord_type   ord_no  line_seq_no   item_no   unit_price
 O         2343        1          PartA      3.00
 O         2343        2          PartB      3.40
 O         2343        3          PartC      5.00
 O         2343        4         REBATE       .75

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:16:06

[code]CREATE #Temp
(ID int IDENTITY(1,1),
ord_type char(1),
ord_no int,
ext_rebate money,
maxseq int)

INSERT INTO #Temp (ord_type,
ord_no,
ext_rebate)
SELECT ord_type,
ord_no,
ext_rebate,
(SELECT MAX(line_seq_no) FROM OEORDLIN_SQL WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)
FROM ORDERREBATE o

INSERT INTO OEORDLIN_SQL
SELECT ord_type,
ord_no,
(SELECT count(*) FROM #Temp WHERE ord_type=t.ord_type AND ord_no=ord_no AND ID <t.ID)+1+MaxSeq,
'REBATE',
ext_rebate
FROM #Temp t

DROP TABLE #Temp[/code]

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 12:27:26

Still need to add the extended rebates together in the temp table.
So would I
INSERT INTO OEORDLIN_SQL
SELECT ord_type,
ord_no,
(SELECT count(*) FROM #Temp WHERE ord_type=t.ord_type AND ord_no=ord_no AND ID <t.ID)+1+MaxSeq,
'REBATE',
(SELECT ORD_TYPE,ORD_NO,SUM(EXT_REBATE)

FROM #Temp t

DROP TABLE #Temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:49:03

[code]CREATE #Temp
(ID int IDENTITY(1,1),
ord_type char(1),
ord_no int,
ext_rebate money,
maxseq int)

INSERT INTO #Temp (ord_type,
ord_no,
ext_rebate)
SELECT ord_type,
ord_no,
SUM(ext_rebate),
(SELECT MAX(line_seq_no) FROM OEORDLIN_SQL WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)
FROM ORDERREBATE o
GROUP BY ord_type,
ord_no

INSERT INTO OEORDLIN_SQL
SELECT ord_type,
ord_no,
MaxSeq+1,
'REBATE',
ext_rebate
FROM #Temp t

DROP TABLE #Temp[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:03:04

in fact you dont need temp table now. you just need this

INSERT INTO OEORDLIN_SQL (ord_type,
ord_no,
line_seq_no,
item_no,
unit_price)
SELECT ord_type,
ord_no,
(SELECT MAX(line_seq_no) FROM OEORDLIN_SQL WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
'REBATE',
SUM(ext_rebate)
FROM ORDERREBATE o
GROUP BY ord_type,
ord_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 13:13:10

I'm back to my cannot insert duplicate Key issue.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 13:26:30

the ord_no and ord_type will that are inserted will exist. But the line_seq_no should not since you are adding 1 to the max that is found. I've also tried just 9999. Since I know the highest one that exists is 689.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:33:27

see the exact definition of unique constraint and check if it involves the line_seq_no column. ALso can there be cases where you've records in OEORDLIN_SQL with NULL values for line_seq_no?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 13:49:23

I created an empty table and ran the script against it. Its trying to insert multiple records with the same line_seq_no. But I haven't quite figured out why its inserting the number of records that it is. Here is what it tried to insert for one order.

type ord_no line_seq item_no Total rebate
C 616435 31 REBATE -84.883500
C 616435 31 REBATE -56.589000
C 616435 31 REBATE -28.294500
C 616435 31 REBATE -311.239500
C 616435 31 REBATE -28.294500
C 616435 31 REBATE -226.356000
C 616435 31 REBATE -56.589000
C 616435 31 REBATE -28.294500
C 616435 31 REBATE -28.294500

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 13:53:42

Just added up what the total should be and it came to 28.3185

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 14:06:12

what does below return?
SELECT ord_type,
ord_no,
'REBATE',
SUM(ext_rebate)
FROM ORDERREBATE o
WHERE ord_type='C'
AND ord_no=616435
GROUP BY ord_type,
ord_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 14:35:56

C 616435 REBATE -28.294500

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 14:58:42

here is the full script that I'm running.

INSERT INTO oeordlinQIV_sql (ord_type,
ord_no,
line_seq_no,
item_no,
loc,
pick_seq,
item_desc_1,
qty_ordered,
qty_to_ship,
unit_price,
request_dt,
qty_return_to_stk,
unit_cost,
promise_dt,
stocked_fg,
controlled_fg,
select_cd,
tot_qty_ordered,
tot_qty_shipped,
req_ship_dt,
billed_dt,
update_fg,
cus_no,
qty_bkord_fg,
line_no,
rma_seq,
recalc_sw)
SELECT distinct o.ord_type,
o.ord_no,
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
'REBATE',
oeordlin_sql.loc,
'99',
'OE REBATE',
-1,
-1,
SUM(ext_rebate),
oeordlin_sql.request_dt,
oeordlin_sql.qty_return_to_stk,
0,
oeordlin_sql.promise_dt,
'N',
'N',
oeordlin_sql.select_cd,
-1,
-1,
oeordlin_sql.req_ship_dt,
oeordlin_sql.billed_dt,
oeordlin_sql.update_fg,
oeordlin_sql.cus_no,
'N',
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
0,
oeordlin_sql.recalc_sw

FROM ORDERREBATE o join oeordlin_sql on o.ord_type = oeordlin_sql.ord_type and o.ord_no = oeordlin_sql.ord_no
where o.ext_rebate <> 0
GROUP BY o.ord_type,
o.ord_no, oeordlin_sql.loc, oeordlin_sql.request_dt,oeordlin_sql.qty_return_to_stk,
oeordlin_sql.promise_dt, oeordlin_sql.select_cd, oeordlin_sql.req_ship_dt,oeordlin_sql.billed_dt,
oeordlin_sql.update_fg,
oeordlin_sql.cus_no, oeordlin_sql.recalc_sw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 23:45:21

why are you having so many items in GROUP BY? thats why its putting the all the extra columns. will you be having different values for
oeordlin_sql? if values will be same you can use like below:-

INSERT INTO oeordlinQIV_sql (ord_type,
ord_no,
line_seq_no,
item_no,
loc,
pick_seq,
item_desc_1, 
qty_ordered,
qty_to_ship, 
unit_price,
request_dt, 
qty_return_to_stk, 
unit_cost, 
promise_dt, 
stocked_fg, 
controlled_fg,
select_cd,
tot_qty_ordered, 
tot_qty_shipped, 
req_ship_dt, 
billed_dt,
update_fg, 
cus_no,
qty_bkord_fg,
line_no, 
rma_seq, 
recalc_sw)
SELECT distinct o.ord_type,
o.ord_no,
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
'REBATE',
oeordlin_sql.loc, 
'99',
'OE REBATE', 
-1, 
-1,
o.TotalRebate,
oeordlin_sql.request_dt,
oeordlin_sql.qty_return_to_stk,
0,
oeordlin_sql.promise_dt,
'N',
'N',
oeordlin_sql.select_cd,
-1,
-1,
oeordlin_sql.req_ship_dt, 
oeordlin_sql.billed_dt,
oeordlin_sql.update_fg, 
oeordlin_sql.cus_no,
'N',
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
0,
oeordlin_sql.recalc_sw

FROM
( 
SELECT ord_type,ord_no,SUM(ext_rebate) AS TotalRebate
FROM ORDERREBATE 
WHERE ext_rebate <> 0
GROUP BY ord_type,ord_no) o 
join oeordlin_sql 
on o.ord_type = oeordlin_sql.ord_type 
and o.ord_no = oeordlin_sql.ord_no 
GROUP BY  oeordlin_sql.loc, oeordlin_sql.request_dt,oeordlin_sql.qty_return_to_stk,
oeordlin_sql.promise_dt, oeordlin_sql.select_cd, oeordlin_sql.req_ship_dt,oeordlin_sql.billed_dt,
oeordlin_sql.update_fg, 
oeordlin_sql.cus_no, oeordlin_sql.recalc_sw

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-08 : 08:34:52

The reason for everything in the group by was that I received the following message for each of those fields:Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

With the above script I received this message.

Msg 8120, Level 16, State 1, Line 1
Column 'o.ord_type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-08 : 08:59:14

Instead of copying values for the fields that were in the group by I put in default values. This is what I have now which seems to work good. Thank you for all of your help.

INSERT INTO oeordlin_sql (ord_type,
ord_no,
line_seq_no,
item_no,
loc,
pick_seq,
item_desc_1,
qty_ordered,
qty_to_ship,
unit_price,
request_dt,
qty_return_to_stk,
unit_cost,
promise_dt,
stocked_fg,
controlled_fg,
select_cd,
tot_qty_ordered,
tot_qty_shipped,
req_ship_dt,
billed_dt,
cus_no,
qty_bkord_fg,
line_no,
rma_seq,
recalc_sw)
SELECT distinct o.ord_type,
o.ord_no,
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
'REBATE',
'BRY',
'99',
'OE REBATE',
-1,
-1,
SUM(ext_rebate),
getdate(),
0,
0,
getdate(),
'N',
'N',
'S',
-1,
-1,
getdate(),
getdate(),
oeordlin_sql.cus_no,
'N',
(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
AND ord_type=o.ord_type)+1,
0,
'Y'

FROM ORDERREBATE o join oeordlin_sql on o.ord_type = oeordlin_sql.ord_type and o.ord_no = oeordlin_sql.ord_no
where o.ext_rebate <> 0
GROUP BY o.ord_type,
o.ord_no,
oeordlin_sql.cus_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-08 : 09:11:05

I think I spoke to soon. The SUM(EXT_REBATE) is calculating incorrectly. For one order the rebate should be 29.046 but it is inserting 1568.48 which is the number of lines on the order which is 54 * 29.046 = 1568.48

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 09:47:51

but dont you require only one record to be inserted for each ord_no,ord_type group? then why should you be joining with oeordlin_sql table?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-08 : 12:42:19

When I take out the join I get a message:
Cannot insert the value NULL into column line_seq_no.

So I just took out
--(SELECT MAX(line_seq_no) FROM oeordlin_sql WHERE ord_no=o.ord_no
--AND ord_type=o.ord_type)+1,

and just put 9999.
That seemed to have solved the amount issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources