• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

INSERT multiple rows

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.
1 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)
Jagadishwor DulalBraces MediaCommented:
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.
BradburtAuthor Commented:
Thanks, nice & simple.
I can dynamically create the query so this is fine.

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now