insert into table where some records exist

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

SQL Server 2000 Forums

Transact-SQL (2000)

insert into table where some records exist

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-12-02 : 17:15:00

Hi,
I need to insert a group of records into a table but some of the items might already exist. Due to primary key constraints it raises errors. I'm not sure how to proceed. Lets say a user has a list of products. They might already have some products but due to some event, they need all the products in that category. Since they might already have some items (and deleting them before inserting is not an option), I'm not sure how to add the remaining items. Please see the sample below. User 1 already has a 'dinner plate' but now I need to add ALL the 'houseware' items. How can I insert the 'coffee cup' and 'fork' without inserting the 'dinner plate'? The input parameters need to be the 'CustomerID' and 'Type'


CREATE TABLE Products (
	[ProductID] [int] NOT NULL ,
	[Type] [varchar] (20) ,
	[Name] [varchar] (100),
)
GO

CREATE TABLE CustomerProducts (
	[CustomerID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
)
GO

ALTER TABLE CustomerProducts ADD 
	CONSTRAINT [PK_CustomerProducts] PRIMARY KEY  CLUSTERED 
	(
		[CustomerID],
		[ProductID]
	)
GO

INSERT INTO Products VALUES (1,'cars','ford')
GO
INSERT INTO Products VALUES (2,'cars','honda')
GO
INSERT INTO Products VALUES (3,'housewares','dinner plate')
GO
INSERT INTO Products VALUES (4,'housewares','coffee cup')
GO
INSERT INTO Products VALUES (5,'housewares','fork')
GO
INSERT INTO CustomerProducts VALUES (1,1)
GO
INSERT INTO CustomerProducts VALUES (1,3)
GO

-- this is where I need a sproc which will essentially
-- insert records based on type and customerID
INSERT INTO CustomerProducts
SELECT
	1
	,ProductID
FROM 
	Products
WHERE
	Type = 'housewares'
GO

Drop table CustomerProducts
GO
Drop table Products
GO

Nic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 17:26:01

INSERT INTO CustomerProducts
SELECT 1, ProductID
FROM Products
WHERE Type = 'housewares' AND ProductID NOT IN (SELECT ProductID FROM CustomerProducts WHERE CustomerID = 1)

Does that work for you?

Tara

nic
Posting Yak Master

209 Posts

Posted - 2003-12-02 : 17:35:26

Yes, thank you! I knew it had to be straight forward I was just getting confused.

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources