Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: Inserting data into composite primary key

    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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Not sure why you'd use SSIS...What's the source of your data? If it's entered through an interactive app, then I doubt you'd need SSIS. Just lookup OUTPUT clause for INSERT statement.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2010
    Posts
    10
    the source of the data is excel in batch format.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, then...do use SSIS, load the data into a temp table, then use SQL Task to call a proc that will use OUTPUT clause to capture your identity column values from client and vendor tables into your clientvendor table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •