inserting data into composite primary key

Question

Post reply

inserting data into composite primary key

T2512

Mr or Mrs. 500

Points: 508
More actions
July 22, 2010 at 12:07 pm

#235678

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.
Table clientvendor
clientid vendorid (composite primarykey)
1 1
1 2
1 3
1 4
1 5
2 2
2 3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

What exactly is your question?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

T2512 Mr or Mrs. 500 Points: 508 More actions · Answer 2

First of all thanx of the quick reply.

Could someone please let me know how would I get the following result;

Table clientvendor

clientid vendorid (composite primarykey)

1 1

1 2

1 3

1 4

1 5

2 2

2 3

I need to insert the vendorid along its associated client id in the junction table in parallel. I need to take the clientid and the vendor id from the client and vendor table and insert it into the junction tables composite primary key.

If i have a single client then this is possible with a easy cross join; but some vendors belong to multiple clients. So how is it possible to the map vendors 2 and 3 with clientid 2 .

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

I really don't understand what you're asking. To do the inserts is simply:

insert into clientvendor (clientid, vendorid)

select 2, 2 union

select 2, 3

Normally you would have some sort of front end application and you would use a stored procedure in SQL Server to do inserts/updates/deletes.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chandrakala.shetty SSC Enthusiast Points: 166 More actions · Answer 4

Try this below statement.

Please note that there is NO WHERE clause; this will give you all the combination.

Insert into clientVendor

select cientId, vendorID

from vendor v

, client