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

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-11-18 : 11:07:01

I have a table that looks like:


ord_no    vend_no    Status
  51       100         P
  51       100         P
  52       100         P
  52       100         P
  53       200         P
  53       200         P
  53       200         C

I need to insert 1 record for each ord_no. The insert I'm using now is inserting a record for each record.
And is it possible to not have it insert if an ord_no already has a record with a status of 'C'?


insert into POHDRHST_SQL(
ord_no,
vend_no, 
curr_cd, 
po_type, 
ord_dt, 
ord_status, 
chg_cancel_cd, 
receive_comp_fg, 
vend_late_cncl_fg, 
print_dt, 
print_post_fg, 
byr_plnr, 
ship_to_cd, 
ship_via_cd, 
ap_terms_cd, 
fob_cd, 
col_ppd_cd, 
acknowledge_fg, 
confirm_fg, 
prt_price_fg, 
ord_weight, 
user_name, 
rcv_exch_rt_fg, 
inv_exch_rt_fg, 
landed_cost_cd, 
landed_cost_cd_2, 
landed_cost_cd_3, 
landed_cost_cd_4, 
landed_cost_cd_5, 
landed_cost_cd_6, 
landed_cost_cd_7, 
landed_cost_cd_8, 
landed_cost_cd_9, 
landed_cost_cd_10, 
cmt_cd_1, 
cmt_1, 
cmt_cd_2,
cmt_2, 
cmt_cd_3, 
cmt_3, 
user_def_fld_1, 
user_def_fld_2, 
user_def_fld_3, 
user_def_fld_4, 
user_def_fld_5, 
vend_alt_adr_cd, 
po_form, rtv_form, 
rcvr_form, 
updt_in_progress, 
filler_0001, 
hst_dt, 
hst_tm, 
id_no, 
jnl_cd, 
batch_id)
SELECT
ord_no,
vend_no, 
curr_cd, 
po_type, 
ord_dt, 
'C', 
chg_cancel_cd, 
receive_comp_fg, 
vend_late_cncl_fg, 
print_dt, 
print_post_fg, 
byr_plnr, 
ship_to_cd, 
ship_via_cd, 
ap_terms_cd, 
fob_cd, 
col_ppd_cd, 
acknowledge_fg, 
confirm_fg, 
prt_price_fg, 
ord_weight, 
user_name, 
rcv_exch_rt_fg, 
inv_exch_rt_fg, 
landed_cost_cd, 
landed_cost_cd_2, 
landed_cost_cd_3, 
landed_cost_cd_4, 
landed_cost_cd_5, 
landed_cost_cd_6, 
landed_cost_cd_7, 
landed_cost_cd_8, 
landed_cost_cd_9, 
landed_cost_cd_10, 
cmt_cd_1, 
cmt_1, 
cmt_cd_2,
cmt_2, 
cmt_cd_3, 
cmt_3, 
user_def_fld_1, 
user_def_fld_2, 
user_def_fld_3, 
user_def_fld_4, 
user_def_fld_5, 
vend_alt_adr_cd, 
po_form, rtv_form, 
rcvr_form, 
updt_in_progress, 
filler_0001, 
hst_dt, 
max(hst_tm)+1, 
id_no, 
jnl_cd, 
batch_id
FROM POHDRHST_SQL
where ord_no < 6000
group by ord_no,
vend_no, 
curr_cd, 
po_type, 
ord_dt, 
ord_status, 
chg_cancel_cd, 
receive_comp_fg, 
vend_late_cncl_fg, 
print_dt, 
print_post_fg, 
byr_plnr, 
ship_to_cd, 
ship_via_cd, 
ap_terms_cd, 
fob_cd, 
col_ppd_cd, 
acknowledge_fg, 
confirm_fg, 
prt_price_fg, 
ord_weight, 
user_name, 
rcv_exch_rt_fg, 
inv_exch_rt_fg, 
landed_cost_cd, 
landed_cost_cd_2, 
landed_cost_cd_3, 
landed_cost_cd_4, 
landed_cost_cd_5, 
landed_cost_cd_6, 
landed_cost_cd_7, 
landed_cost_cd_8, 
landed_cost_cd_9, 
landed_cost_cd_10, 
cmt_cd_1, 
cmt_1, 
cmt_cd_2,
cmt_2, 
cmt_cd_3, 
cmt_3, 
user_def_fld_1, 
user_def_fld_2, 
user_def_fld_3, 
user_def_fld_4, 
user_def_fld_5, 
vend_alt_adr_cd, 
po_form, rtv_form, 
rcvr_form, 
updt_in_progress, 
filler_0001, 
hst_dt, 
hst_tm, 
id_no, 
jnl_cd, 
batch_id

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 11:11:36

insert table t1
select...
WHERE NOT EXISTS(select * from table where ord_no=t1.ord_no and Status='C')

No, you're never too old to Yak'n'Roll if you're too young to die.

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-11-18 : 11:20:03

Using this I get Incorrect syntax near t1

insert pohdrhst_sql t1(
ord_no,
vend_no,
curr_cd,
po_type,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-18 : 11:45:05

Oops! My bad...sorry
insert pohdrhst_sql(
ord_no,
vend_no,
curr_cd,
po_type,
...)
select
ord_no,
vend_no,
curr_cd,
po_type,
...
from pohdrhst_sql t1
WHERE NOT EXISTS(select * from pohdrhst_sql where ord_no=t1.ord_no and Status='C')

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 11:58:35

Qualify the tables names!


INSERT INTO POHDRHST_SQL (
	ord_no,
	vend_no, 
	curr_cd, 
	po_type, 
	ord_dt, 
	ord_status, 
	chg_cancel_cd, 
	receive_comp_fg, 
	vend_late_cncl_fg, 
	print_dt, 
	print_post_fg, 
	byr_plnr, 
	ship_to_cd, 
	ship_via_cd, 
	ap_terms_cd, 
	fob_cd, 
	col_ppd_cd, 
	acknowledge_fg, 
	confirm_fg, 
	prt_price_fg, 
	ord_weight, 
	USER_NAME, 
	rcv_exch_rt_fg, 
	inv_exch_rt_fg, 
	landed_cost_cd, 
	landed_cost_cd_2, 
	landed_cost_cd_3, 
	landed_cost_cd_4, 
	landed_cost_cd_5, 
	landed_cost_cd_6, 
	landed_cost_cd_7, 
	landed_cost_cd_8, 
	landed_cost_cd_9, 
	landed_cost_cd_10, 
	cmt_cd_1, 
	cmt_1, 
	cmt_cd_2,
	cmt_2, 
	cmt_cd_3, 
	cmt_3, 
	user_def_fld_1, 
	user_def_fld_2, 
	user_def_fld_3, 
	user_def_fld_4, 
	user_def_fld_5, 
	vend_alt_adr_cd, 
	po_form, rtv_form, 
	rcvr_form, 
	updt_in_progress, 
	filler_0001, 
	hst_dt, 
	hst_tm, 
	id_no, 
	jnl_cd, 
	batch_id
	)
SELECT
	ord_no,
	vend_no, 
	curr_cd, 
	po_type, 
	ord_dt, 
	'C', 
	chg_cancel_cd, 
	receive_comp_fg, 
	vend_late_cncl_fg, 
	print_dt, 
	print_post_fg, 
	byr_plnr, 
	ship_to_cd, 
	ship_via_cd, 
	ap_terms_cd, 
	fob_cd, 
	col_ppd_cd, 
	acknowledge_fg, 
	confirm_fg, 
	prt_price_fg, 
	ord_weight, 
	USER_NAME, 
	rcv_exch_rt_fg, 
	inv_exch_rt_fg, 
	landed_cost_cd, 
	landed_cost_cd_2, 
	landed_cost_cd_3, 
	landed_cost_cd_4, 
	landed_cost_cd_5, 
	landed_cost_cd_6, 
	landed_cost_cd_7, 
	landed_cost_cd_8, 
	landed_cost_cd_9, 
	landed_cost_cd_10, 
	cmt_cd_1, 
	cmt_1, 
	cmt_cd_2,
	cmt_2, 
	cmt_cd_3, 
	cmt_3, 
	user_def_fld_1, 
	user_def_fld_2, 
	user_def_fld_3, 
	user_def_fld_4, 
	user_def_fld_5, 
	vend_alt_adr_cd, 
	po_form, rtv_form, 
	rcvr_form, 
	updt_in_progress, 
	filler_0001, 
	hst_dt, 
	MAX(hst_tm)+1, 
	id_no, 
	jnl_cd, 
	batch_id
FROM
	POHDRHST_SQL t1
WHERE
	ord_no < 6000
GROUP BY
	ord_no,
	vend_no, 
	curr_cd, 
	po_type, 
	ord_dt, 
	ord_status, 
	chg_cancel_cd, 
	receive_comp_fg, 
	vend_late_cncl_fg, 
	print_dt, 
	print_post_fg, 
	byr_plnr, 
	ship_to_cd, 
	ship_via_cd, 
	ap_terms_cd, 
	fob_cd, 
	col_ppd_cd, 
	acknowledge_fg, 
	confirm_fg, 
	prt_price_fg, 
	ord_weight, 
	USER_NAME, 
	rcv_exch_rt_fg, 
	inv_exch_rt_fg, 
	landed_cost_cd, 
	landed_cost_cd_2, 
	landed_cost_cd_3, 
	landed_cost_cd_4, 
	landed_cost_cd_5, 
	landed_cost_cd_6, 
	landed_cost_cd_7, 
	landed_cost_cd_8, 
	landed_cost_cd_9, 
	landed_cost_cd_10, 
	cmt_cd_1, 
	cmt_1, 
	cmt_cd_2,
	cmt_2, 
	cmt_cd_3, 
	cmt_3, 
	user_def_fld_1, 
	user_def_fld_2, 
	user_def_fld_3, 
	user_def_fld_4, 
	user_def_fld_5, 
	vend_alt_adr_cd, 
	po_form, rtv_form, 
	rcvr_form, 
	updt_in_progress, 
	filler_0001, 
	hst_dt, 
	hst_tm, 
	id_no, 
	jnl_cd, 
	batch_id
WHERE
	NOT EXISTS(SELECT 1 FROM POHDRHST_SQL t2 WHERE t2.[ord_no] = t1.[ord_no] AND t2.[status] = 'C')

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-11-18 : 14:01:48

Now I'm getting Server: Msg 156, Level 15, State 1, Line 174
Incorrect syntax near the keyword 'WHERE'.

Does the Not exists need to go up where the first where is?

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-11-18 : 14:26:41

I got the script to run by moving the last where statement after the From. It doesn't insert a record if it finds a C

But it is still inserting two records for order 51 and 52 when I only want it to insert one record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources