December 12, 2008 at 9:26 am
Hi there,
I have a stored procedure that inserts data into a linked server's table if the data does not already exist. This has been working fine, I use the following:
INSERT INTO SHOP.shop.dbo.tblPricebook (item, [desc], uom, manid, price, pricegroup, pricegroup_desc, pricegroup_order)
SELECT item, [desc], uom, manid, price, pricegroup, pricegroup_desc, pricegroup_order
FROM vwValidPriceBookItems
WHERE item COLLATE database_default NOT IN (SELECT item FROM SHOP.shop.dbo.tblPricebook)
/****** Object: Table [dbo].[tblPricebook] Script Date: 12/12/2008 16:20:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPricebook](
[item] [varchar](42) NOT NULL,
[desc] [varchar](100) NOT NULL,
[uom] [varchar](50) NOT NULL,
[manid] [int] NOT NULL,
[price] [varchar](10) NOT NULL,
[pricegroup] [varchar](12) NOT NULL,
[pricegroup_desc] [varchar](50) NOT NULL,
[pricegroup_order] [decimal](12, 1) NOT NULL CONSTRAINT [DF_tblPricebook_pricegroup_order] DEFAULT (0),
CONSTRAINT [PK_tblPricebook] PRIMARY KEY CLUSTERED
(
[item] ASC,
[uom] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
This is an example of what the data in vwValidPriceBookItems looks like:
item [desc] manID uom price pricegroup pricegroup_desc pricegroup_order
MDNF-B4MDG 4 Litres Bottle MDG Neutral Fluid37Box of 4322.00MDG FLUIDMDG Fluid (85.1)85.1
MDNF-B4MDG 4 Litres Bottle MDG Neutral Fluid37Each80.50MDG FLUIDMDG Fluid (85.1)85.1
This is example data of what already exists in the table (SHOP.shop.dbo.tblPricebook) on the linked server:
item [desc] manID uom price pricegroup pricegroup_desc pricegroup_order
MDNF-B4MDG 4 Litres Bottle MDG Neutral FluidEach3780.50MDG FLUIDMDG Fluid (85.1)85.1
The insert statement sees this row as its only looking at the item column, so doesn't insert the additional row. When I setup the database the item was unique column, it is now item and uom.
I need help rewriting the insert so that it looks at both item and uom. I don't think I can use my current NOT IN, should I be using a join?
Any ideas are welcome.
Cheers,
Steve
December 12, 2008 at 9:36 am
INSERT INTO SHOP.shop.dbo.tblPricebook (item, [desc], uom, manid, price, pricegroup, pricegroup_desc, pricegroup_order)
SELECT PBI.item, PBI.[desc], PBI.uom, PBI.manid, PBI.price, PBI.pricegroup, PBI.pricegroup_desc, PBI.pricegroup_order
FROM vwValidPriceBookItems PBI
LEFT JOIN SHOP.shop.dbo.tblPricebook P ON PBI.item = P.item AND PBI.uom = P.uom
WHERE P.Item IS NULL
December 12, 2008 at 9:39 am
Edited that query, so if you're looking at it like "Is that guy nuts?", yeah, so was I. Not sure how I got that all messed up =).
December 12, 2008 at 9:45 am
Seth, thank you, I did think you were nuts to begin with but I saw your error and was about to correct it. Due to different collations I added the fix, and it looks good to me.
I had just got it all wrong when you posted, I was doing the join with <> instead of = and it was returning millions of duplicate rows. I will be honest, I don't know why this works, to me it seems the opposite of what I was thinking 🙂
Steve
December 12, 2008 at 9:58 am
The logic behind it is this. It's a very common use of outer joins that makes a lot of things very easy.
[font="Courier New"]DECLARE @YourView TABLE(
I INT,
V CHAR(1))
DECLARE @YourTable TABLE(
I INT,
V CHAR(1))
INSERT INTO @YourView(I,V)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C'
INSERT INTO @YourTable
SELECT 1,'A' UNION ALL
SELECT 2,'F' UNION ALL -- Different value than the View
SELECT 3,'C'
SELECT V.I, -- Show all rows from your view
T.V -- Show the matching values that exist in your table
FROM @YourView V
LEFT JOIN @YourTable T ON V.I = T.I AND V.V = T.V -- Left join ensures all rows from your view returned
--- RESULTS ---
--- 1 A
--- 2 NULL -- This means that a row with combination of I and V does nto exist in your Table.
--- 3 C
-- Continuing with that logic...
SELECT V.I,
T.V
FROM @YourView V
LEFT JOIN @YourTable T ON V.I = T.I AND V.V = T.V
WHERE T.V IS NULL -- Only show ones that don't have matching rows in Your Table.
-- Note that you could have used T.I as well, the whole row from your table is NULL due to the join.
SELECT V.I,
T.V
FROM @YourView V
LEFT JOIN @YourTable T ON V.I = T.I AND V.V = T.V
WHERE T.I IS NULL -- Only show ones that don't have matching rows in Your Table.[/font]
December 12, 2008 at 10:12 am
Hi Seth,
That is making sense now, thank you for explaining it.
Now I just need to get SSMS to create insert data sql for to me to post sample data here, then I will be happy 🙂
Cheers,
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply