Looping through Column to Insert

Attempting to insert a new Item for each Vendor into a Vendor Item Catalog. However, the Vendors are in no particular order.

ie... sample data

CompanyID,VendorID,ItemID

Comp1,1004,Event1

Comp1,3433,Event2

Comp1,45343,Event3

I need to be able to loop through to the next Vendor that doesnt already have this ItemID.

Any ideas?

Maybe something like this:

declare @.source table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.source
select 'Comp1', 1004, 'Event1' union all
select 'Comp1', 3433, 'Event2' union all
select 'Comp1',45343, 'Event3'

declare @.target table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.target
select 'Comp1', 1004, 'Event1'

insert into @.target

select CompanyID,
VendorID,
ItemID
from @.source a
where not exists
( select 0 from @.target b
where a.companyId = b.companyId
and a.vendorId = b.vendorId
and a.ItemId = b.ItemId
)

select * from @.target
/*
CompanyID VendorID ItemID
-- --
Comp1 1004 Event1
Comp1 3433 Event2
Comp1 45343 Event3
*/

|||

Are you trying to INSERT or UPDATE?

Adamus

|||

I am attempting to INSERT. I tried using the suggested code WHERE NOT EXISTS but it returned nothing.

|||It would be good if you post the code you are executing with a description of the tables involved.|||

CODE:

declare @.CompID as varchar(5),

@.VendID as varchar(15)

SET @.CompID = SELECT CompanyID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 CompanyID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

SET @.VendID = SELECT VendorID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 VendorID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

INSERT INTO ARIVendorCatalog

VALUES (@.CompID, @.VendID, 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '','','')

I Figure that once inserted, the TOP vendor will change every time until there is none left.

|||

Ilana:

You might be able to accomplish the insert with something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from ( select CompID,
VendorID,
max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) as hasTargetEvent
from ARIVendorCatalog
group by CompID, VendorID
having max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) = 0
) source

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/

|||

Ilana:

There is a much better way of doing this if you happen to also have a "VENDOR" table that lists all unique active combinations of CompID and VendorID. If that is true you will not need to compute the aggregate in my last example and you might be able to do something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

create table Vendor
( CompID varchar(5),
VendorID varchar(15),
vendorName varchar(20)
)
go

insert into Vendor
select 'Comp1', 'Vend1', 'Jaba Vendor' union all
select 'Comp1', 'Vend2', 'Blue Vendor' union all
select 'Comp2', 'Vend3', 'Gold Vendor' union all
select 'Comp2', 'Vend4', 'New Vendor'

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from Vendor a
where not exists
( select 0 from ARIVendorCatalog b
where a.CompID = b.CompID
and a.VendorID = b.VendorID
and VendorItemId = 'Events - Internal Meetings'
)

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend4 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/