Insert multiple rows from another table

Question

I have a temptable in my query which could have certain records like

Id Name
34 one
35 two
65 five

Now for each row in temp table, I want to insert a new row in history table

So the history table now would have 3 new records

Id   created date   updatedby
34    createdDate
35    createdDate
65    createdDate

updated by will remain same and a parameter in query will hold that value.except updated by I also have some other columns but that will remain constants and will pick from parameters supplied to query. only id needs to be picked up from temp table — Sweetie
– Sweetie, Commented Mar 5, 2017 at 16:59

Gurwinder Singh · Accepted Answer · 2017-03-05 16:54:46Z

8

You can insert using select.

Something like this:

insert into history (id, created_date, updatedBy)
select id, getdate(), 'add person here??'
from #temptable;

The above require the updatedBy to be supplied in the select query.

If you want current user, you could use SUSER_NAME()

answered Mar 5, 2017 at 16:54

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

will it generate new row in history automatically for each row in temp table?

Yes. It will insert one row for each row in temptable.