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

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-02 : 12:49:18

Have a record I want to insert into a table. oeordlin

Have a second table I want to add a column and put the sum into the oeordlin.


Records in oeordlin are as follows:
ord_type   ord_no   item_no line_no   Qty_ord  UnitPrice
   O        233     item2    1           6      1.00
   O        233     item45   2           4      3.00
   O        233     item44   3           3      4.00

Records in orderrebate are basically identical. But there will be a field called Ext_rebate which I want to total and insert into the oeordlin above. 

ord_type   ord_no   item_no line_no   Qty_ord  UnitPrice
   O        233     Rebate    4           -1      5.00


here is what I have so far. What I'm trying to do is pull some of the default values from one of the lines in the oeordlin and input some of the values I want inserted. 

insert into oeordlin_sql
(ord_type,
ord_no,
item_no,
line_no,
qty_ord,
unit_price)
select
oeordlin_sql.ord_type,
oeordlin_sql.ord_no,
'REBATE',
9999,
-1,
SUM(ORDERREBATE.EXT_REBATE)
FROM OEORDLIN_SQL JOIN ORDERREBATE ON OEORDLIN_SQL.ORD_TYPE = ORDERREBATE.ORD_TYPE AND OEORDLIN_SQL.ORD_NO = ORDERREBATE.ORD_NO

X002548
Not Just a Number

15586 Posts

Posted - 2008-10-02 : 14:59:13

What's the problem?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-02 : 15:04:16

GROUP BY oeordlin_sql.ord_type,
oeordlin_sql.ord_no ???

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 23:59:38

For each line in first table you want to update same total value?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-03 : 15:30:44

The orderrebate table and oeordlin table will have identical records. What I'm trying to accomplish is adding up the Ext_rebate field in the orderrebate table and inserting a new record into the oeordlin table as a new item on the order. That item on the order will hold the total rebate amount.

I'm unable to get anything inserted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 02:31:45

[code]insert into oeordlin_sql
(ord_type,
ord_no,
item_no,
line_no,
qty_ord,
unit_price)
select
os.ord_type,
os.ord_no,
'REBATE',
9999,
-1,
or.TotalRebate
FROM OEORDLIN_SQL os
JOIN (SELECT ORD_TYPE,ORD_NO,SUM(EXT_REBATE) AS TotalRebate
FROM ORDERREBATE
GROUP BY ORD_TYPE,ORD_NO)or
ON os.ORD_TYPE = or.ORD_TYPE
AND os.ORD_NO = or.ORD_NO[/code]

Vack
Aged Yak Warrior

530 Posts

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

I'm getting several of these messages when executing the statement above.

he multi-part identifier "oeordlin_sql.ord_type" could not be bound.
Msg 4104, Level 16, State 1, Line 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:41:28

quote:
Originally posted by Vack

I'm getting several of these messages when executing the statement above.

he multi-part identifier "oeordlin_sql.ord_type" could not be bound.
Msg 4104, Level 16, State 1, Line 1

post your used query. My posted query does not have a column like oeordlin_sql.ord_type

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 10:01:03

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,
update_fg,
cus_no,
qty_bkord_fg,
line_no,
rma_seq,
recalc_sw)
SELECT
oeordlin_sql.ord_type,
oeordlin_sql.ord_no,
9999,
'RBATE',
oeordlin_sql.loc,
'99',
'OE REBATE',
-1,
-1,
orderrebate.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',
9999,
0,
oeordlin_sql.recalc_sw
FROM OEORDLIN_SQL os
JOIN (SELECT ORD_TYPE,ORD_NO,SUM(EXT_REBATE) AS TotalRebate
FROM ORDERREBATE
GROUP BY ORD_TYPE,ORD_NO)orderrebate
ON os.ORD_TYPE = orderrebate.ORD_TYPE
AND os.ORD_NO = orderrebate.ORD_NO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 10:05:49

quote:
Originally posted by Vack

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,
update_fg,
cus_no,
qty_bkord_fg,
line_no,
rma_seq,
recalc_sw)
SELECT
os ~~oeordlin_sql~~.ord_type,
os ~~oeordlin_sql~~.ord_no,
9999,
'RBATE',
os ~~oeordlin_sql~~.loc,
'99',
'OE REBATE',
-1,
-1,
orderrebate.TotalRebate,
os ~~oeordlin_sql~~.request_dt,
os ~~oeordlin_sql~~.qty_return_to_stk,
0,
os ~~oeordlin_sql~~.promise_dt,
'N',
'N',
os ~~oeordlin_sql~~.select_cd,
-1,
-1,
os ~~oeordlin_sql~~.req_ship_dt,
os ~~oeordlin_sql~~.billed_dt,
os ~~oeordlin_sql~~.update_fg,
os ~~oeordlin_sql~~.cus_no,
'N',
9999,
0,
os ~~oeordlin_sql~~.recalc_sw
FROM OEORDLIN_SQL os
JOIN (SELECT ORD_TYPE,ORD_NO,SUM(EXT_REBATE) AS TotalRebate
FROM ORDERREBATE
GROUP BY ORD_TYPE,ORD_NO)orderrebate
ON os.ORD_TYPE = orderrebate.ORD_TYPE
AND os.ORD_NO = orderrebate.ORD_NO

modify as above

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 10:31:21

Now I get:
Incorrect syntax near ',' line 30
incorrect syntax near 'orderrebate line 60.

Nothing jumps out at me.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-10-07 : 10:35:58

Ignore my last. I forgot to delete the oeordlin_sql. So now my syntax check is ok. When I run it i get

Cannot insert duplicate key row in object oeordlin_sql with unique index. ioeordlin_sql0
the keys in this index are.
ord_type
ord_no
line_seq_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:00:48

quote:
Originally posted by Vack

Ignore my last. I forgot to delete the oeordlin_sql. So now my syntax check is ok. When I run it i get

Cannot insert duplicate key row in object oeordlin_sql with unique index. ioeordlin_sql0
the keys in this index are.
ord_type
ord_no
line_seq_no

make sure you dont have record existing in table with same values for ord_type
ord_no
line_seq_no
columns as you're passing now.

Vack
Aged Yak Warrior

530 Posts

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

there will be for ord_type and ord_no, but I'm passing 9999 for the line_seq_no which I know does not exist.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:22:12

quote:
Originally posted by Vack

there will be for ord_type and ord_no, but I'm passing 9999 for the line_seq_no which I know does not exist.

if it does not exist then it will not throw error. since error happens its sure that such a record exists already.
suggest you to wrap insert inside IF NOT EXISTS () condition which checks if record is already present

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources