INSERT multiple rows

Posted on 2011-05-06
Last Modified: 2012-05-11
Have forgotten more SQL than I learnt, which was not much.
I want to copy a template list of steps from one table (the template) into another table (the active, in use table). The active table is as the template table but also has an Owner column. The Owner column is filled to the user supplied value. So on every copy I must also fill the Owner column on the just inserted rows to the user supplied value. Later another copy may take place with another owner.
My question is how to do this with a single SQL statement such that I avoid concurrency issues.
I remember that I can:
INSERT INTO ActiveSteps (StepName, StepDesc) SELECT StepName, StepDesc FROM TemplateSteps
but how would I get OwnerName into ActiveSteps when OwnerName is a operator supplied value?
I could create a temporary table but would then have multiple steps and concurrency issues.
I do not want to lock the database to avoid concurrency, I am looking for an elegant single SQL line if possible.
Question by:Bradburt
    LVL 7

    Accepted Solution

    if you can create SQL dynamicaly you can create SQL like this:

    INSERT INTO ActiveSteps (StepName, StepDesc, OwnerName) (SELECT StepName, StepDesc,'John Smith' FROM TemplateSteps)
    LVL 15

    Expert Comment

    by:Jagadishwor Dulal
    If you have same field name and data type + not heavy data you can export data to sql and change table name and run sql query to mysql.

    Otherwise you can do it like gsiric.

    Author Closing Comment

    Thanks, nice & simple.
    I can dynamically create the query so this is fine.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now