Adding rows to SQL table depending on key values from another table

Posted on 2014-08-18
Last Modified: 2014-08-20
I have three tables.

Table A - Vendor Contracts Table
Table B - Vendor Agreement Details
Table C - Vendor Agreement codes and descriptions

What I need to do is for every vendor in table A,  add rows to table B for each of the rows in table C.

So for instance I have Vendor 1234567890 in Table A, I want to add rows to table B for that vendor for every "Vnn" row in table C.   "Vnn" is just a serious of codes from V1 - V9.  

There are currently a total of 9 "Vnn" codes and there respective descriptions in Table C.   So in Table B I would end up with the following:

Vendor                           Code
123456789                      V01
123456789                      V02
123456789                      V09

and of course for each of the other Vendors in table A.

No need to worry to much about what other columns will be selected/added.  I just need to know what the structure/syntax would look like to do this.
Question by:66chawger
    LVL 1

    Accepted Solution

    I think this is what you are looking for.  Insert from select?
    DECLARE @a TABLE (ID int)

    DECLARE @b TABLE ( tableAID int, details varchar(10))

    DECLARE @c TABLE (tableAID INT ,details VARCHAR(10))

    INSERT INTO @a (id) VALUES (1)
    INSERT INTO @a (id) VALUES (2)
    INSERT INTO @a (id) VALUES (3)

    INSERT INTO @c (tableAID,Details) VALUES (3,'Text 3')
    INSERT INTO @c (tableAID,Details) VALUES (5,'Text 5')
    INSERT INTO @c (tableAID,Details) VALUES (1,'Text 1')
    INSERT INTO @c (tableAID,Details) VALUES (1,'Text 11')
    INSERT INTO @c (tableAID,Details) VALUES (1,'Text 111')

    INSERT INTO @B (tableaid,details)
    SELECT  c.tableaid,c.details FROM @c  c INNER JOIN @a a ON = c.tableaid

    SELECT * FROM @b

    tableAID      details
    3      Text 3
    1      Text 1
    1      Text 11
    1      Text 111

    Author Comment

    So in the example above, you are using "tableAID" to select all the information from the various tables, then using this for the resulting insert, correct?
    LVL 1

    Expert Comment

    by:Robert Lind
    Yes.  In your case, you would have to match the ID's for your tables, from your example that would be the vendor column in tables a and c.

    Author Comment

    I was making this waaaaay to difficult.. LOL.  My mindset has been tied up so much in SP's and TSQL that I forgot to think simple!

    Thanks for the example.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Copy only dates 3 60
    SQL Question 9 34
    Help with SQL 9 62
    indexed table vs unindexed table 19 50
         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now