July 22, 2010 at 12:07 pm
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
July 22, 2010 at 12:17 pm
July 22, 2010 at 12:23 pm
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 .
July 22, 2010 at 2:02 pm
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.
July 22, 2010 at 2:20 pm
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy