Inserting data into composite primary key

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

Inserting data into composite primary key

Author

Topic

aisha09
Starting Member

6 Posts

Posted - 2010-07-22 : 13:54:46

Table vendor
vendorid(primarykey) vendorname
1 target
2 med
3 mdi
4 cashwise
5 walmart

Table client
clientid(primarykey) clientname
1 A

Table clientvendor
clientid vendorid (composite primarykey)
1 1
1 2
1 3
1 4
1 5
Rule: 1 client has many vendors and many vendors may belong to many clients.

I have to insert the vendorid and the clientid from the client and vendor table into clientvendor junction table. Since I have just one client; I was able to insert the records into the junction table with the help of a cross join.

If I have another client record into the client table inserted as follows; and vendorids 2 and 3 also belong to client B;

clientid(primarykey) clientname
1 A
2 B

Could someone please let me know how would I get the following result; I must also note that I will be dealing with thousands of common vendors data between two or three clients. Is it possible with SSIS?

Table clientvendor
clientid vendorid (composite primarykey)
1 1
1 2
1 3
1 4
1 5
2 2
2 3

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-22 : 14:20:53

How do you know that VendorIDs 2 and 3 belong to Client B? Are you given the VendorName or the VendorID or something else?

This seem like a simple ETL type process so, I guess my real question is: Do you need help with the process or just doing an update to the ClinetVendor table? here is some code to update the ClientVendor table. If you need more help please let us know:

-- Setup Sample Data
DECLARE @Vendor TABLE (VendorID INT NOT NULL PRIMARY KEY, VendorName VARCHAR(50))
INSERT @Vendor
SELECT 1, 'target'
UNION ALL SELECT 2, 'med'
UNION ALL SELECT 3, 'mdi'
UNION ALL SELECT 4, 'cashwise'
UNION ALL SELECT 5, 'walmart'

DECLARE @Client TABLE (ClientID INT NOT NULL PRIMARY KEY, ClientName VARCHAR(50))
INSERT @Client
SELECT 1, 'A'

DECLARE @ClientVendor TABLE (ClientID INT NOT NULL,  VendorID INT NOT NULL, PRIMARY KEY(ClientID, VendorID)) 
INSERT @ClientVendor
SELECT 1, 1 
UNION ALL SELECT 1, 2 
UNION ALL SELECT 1, 3 
UNION ALL SELECT 1, 4 
UNION ALL SELECT 1, 5

-- Add New Client
INSERT @Client 
SELECT 2, 'B'

-- Insert ClientVendor missing rows.
INSERT 
	@ClientVendor
SELECT 
	T.ClientID,
	T.VendorID
FROM
	@ClientVendor AS VC
RIGHT OUTER JOIN
	(
		-- Match Client to Vendors via magical IDs..
		SELECT 
			C.ClientID,
			V.VendorID
		FROM 
			@Client AS C
		CROSS JOIN 
			@Vendor AS V
		WHERE
			C.ClientID = 2
			AND V.VendorID IN (2, 3) 
	) AS T
	ON VC.ClientID = T.ClientID
	AND VC.VendorID = T.VendorID
WHERE
	VC.ClientID IS NULL

-- verify results
SELECT *
FROM @ClientVendor

aisha09
Starting Member

6 Posts

Posted - 2010-07-22 : 14:45:07

Thank you so much for your help!!! I really appreciate it...

I also need help with the ETL process, right now we just have one client and the associated vendor data. But in future we would be receiving vendor data in batch format from our second client.

The common vendors between two clients may range upto hundreds. So I may not be able to mention a specific vendor id as u did in the code. I was also trying using SSIS; but am not able to find the correct way to load the data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources