0 rows affected on 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

General SQL Server Forums

New to SQL Server Programming

0 rows affected on insert

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-23 : 13:00:36

I have a new record in the oecusitem table that is not inserting into the IA table.

I'm trying to write the script so that it can be ran multiple times and only insert new records from the IA table.


insert into IA
(
ItemCode,
Accountcode,
CustItem)
select
oecusitem.item_no,
cicmpy.cmp_wwn,
oecusitem.cus_item_no
from oecusitm_sql join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
join IA on oecusitm_sql.item_no = IA.ItemCode and oecusitm_sql.cus_item_no = IA.ItemCodeAccount
           where not exists(select * from IA t1 where t1.ItemCode = IA.ItemCode and t1.CustItem = IA.CustItem)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-23 : 13:37:54

You are inner joining to the table where it doesn't exist..? If so, you are never going to get and ROWs returned. Remove the join and the NOT EXISTS clause should handle the rest. Or you can change the INNER JOIN to a LEFT JOIN (checking for NULL) and remove the NOT EXISTS clause.

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-23 : 14:21:32

I'm not sure if I understood your suggestion. I changed it to the following and got "multi part identifier "IA.ItemCode could not be bound""

from oecusitm_sql join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
where not exists(select * from ItemAccounts t1 where t1.ItemCode = ItemAccounts.ItemCode and t1.ItemCodeAccount=ItemAccounts.ItemCodeAccount)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-23 : 14:27:32

Thats what Lamprey said:

insert into IA
(
ItemCode,
Accountcode,
CustItem
)
select oecusitem.item_no,
cicmpy.cmp_wwn,
oecusitem.cus_item_no
from oecusitm_sql
join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
--join IA on oecusitm_sql.item_no = IA.ItemCode
-- and oecusitm_sql.cus_item_no = IA.ItemCodeAccount
where not exists ( select 1
from IA t1
where t1.ItemCode = IA.ItemCode
and t1.CustItem = IA.CustItem )

--------------------------
http://connectsql.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-23 : 14:50:29

Yop didn't post any DDL, DML, sample data or expected output so it's hard to match up columns for you. But basically you need to change the column aliases on your not exists clause:

insert into IA
(
ItemCode,
Accountcode,
CustItem
)
select
	oecusitem.item_no,
	cicmpy.cmp_wwn,
	oecusitem.cus_item_no
from 
	oecusitm_sql 
join 
	cicmpy 
	on oecusitm_sql.cus_no = cicmpy.debcode
--join 
--	IA 
--	on oecusitm_sql.item_no = IA.ItemCode 
--	and oecusitm_sql.cus_item_no = IA.ItemCodeAccount
where 
	not exists
	(
		select * 
		from IA t1 
		where -- Somthing here is probalby wrong...
			t1.ItemCode = oecusitm_sql.ItemCode 
			and t1.CustItem = oecusitm_sql.CustItem	)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-23 : 14:58:13

insert into IA
(
ItemCode,
Accountcode,
CustItem
)
select oecusitem.item_no,
cicmpy.cmp_wwn,
oecusitem.cus_item_no
from oecusitm_sql
join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
--join IA on oecusitm_sql.item_no = IA.ItemCode
-- and oecusitm_sql.cus_item_no = IA.ItemCodeAccount
where not exists ( select 1
from IA t1
where t1.ItemCode = oecusitem.item_no
and t1.CustItem = oecusitem.cus_item_no )

--------------------------
http://connectsql.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-23 : 15:08:33

Still No luck.

Table IA:
ItemCode AccountCode CustItem
1234 ABC C1234
2222 BBB C2222

Table: oecusitm_sql
Item_no Cus_no cus_item_no
1234 ABC C1234
2222 BBB C2222
3232 ABC C3232

After I run my script I want the IA table to look the same as the oecusitm_sql table. If I keep adding records to the oecusitm_sql table I want to be able to keep running the scrip without getting cannot insert duplicate key row errors.

Does that help?

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-23 : 15:13:41

This one did work. It was posted during my last reply.

insert into IA
(
ItemCode,
Accountcode,
CustItem
)
select oecusitem.item_no,
cicmpy.cmp_wwn,
oecusitem.cus_item_no
from oecusitm_sql
join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
--join IA on oecusitm_sql.item_no = IA.ItemCode
-- and oecusitm_sql.cus_item_no = IA.ItemCodeAccount
where not exists ( select 1
from IA t1
where t1.ItemCode = oecusitem.item_no
and t1.CustItem = oecusitem.cus_item_no )

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-23 : 15:20:47

CREATE TABLE #oecusitm_sql (Item_no INT, Cus_no VARCHAR(50), cus_item_no VARCHAR(50))
CREATE TABLE #IA(ItemCode INT, AccountCode VARCHAR(50), CustItem VARCHAR(50))
GO
INSERT INTO #IA
SELECT 1234 ,'ABC', 'C1234' UNION ALL
SELECT 2222 ,'BBB', 'C2222'

INSERT INTO #oecusitm_sql
SELECT 1234, 'ABC', 'C1234' UNION ALL
SELECT 2222, 'BBB', 'C2222' UNION ALL
SELECT 3232, 'ABC', 'C3232'

--IF ALL COLUMNS TO COMPARE
--INSERT INTO #IA
--SELECT * FROM #oecusitm_sql
--EXCEPT
--SELECT * FROM #IA

----WITH NOT EXISTS

insert into #IA
(
ItemCode,
Accountcode,
CustItem
)
select Item_no,
Cus_no,
cus_item_no
from #oecusitm_sql
where not exists ( select 1
from #IA t1
where t1.ItemCode = #oecusitm_sql.item_no
and t1.CustItem = #oecusitm_sql.cus_item_no )
DROP TABLE #IA
DROP TABLE #oecusitm_sql

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources