SQL - Insert multiple records from select

Question

I have been searching all day but could not find answer to this:

I have a table on SQL Server:

dbo.Program

with fields:

Program.id...PK autoincrement
Program.user...varchar
Program.program...varchar
Program.installed...boolean
Program.department...varchar
Program.wheninstalled...date

Now, I want to insert a new record for every distinct user and copy the department from his latest(Program.wheninstalled) record with other values the same for every user:

Program.user...every unique user
Program.program...MyMostAwesomeProgram
Program.installed...false
Program.department...department of the record with the latest program.wheninstalled field of all the records of the unique user in program.user
Program.wheninstalled...null

I know how to do it in an ugly way:

select the latest records for every user and their department in that record
extract values from 1) and make it into insert into (field1, field2...fieldX) values (1records_value1, 1records_value2...1records_valueX), (2records_value1, 2records_value2...2records_valueX), ... (Nrecords_value1, Nrecords_value2...Nrecords_valueX)

but I would like to know how to do it in a better way. Oh I cannot use some proper HR databse to make my life easier so this is what I got to work with now.

paparazzo · Answer 1 · 2016-09-30T19:49:52.917

1

you said you know how to do the select

insert into Program (user, program, installed, department, wheninstalled)
select user, 'MyMostAwesomeProgram', 'false' , department, null 
from ...

edited Sep 30 '16 at 19:49

answered Sep 30 '16 at 19:41

paparazzo

44,497
23
105
176

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

I'm a postgres guy, but something akin to the below should work:

insert into Program (user, program, installed, department, wheninstalled)
select user, 
   'MyMostAwesomeProgram', 
   false, 
   (select department from someTable where u.user = ...),
   null
from users as u;

from https://stackoverflow.com/a/23905173/3430807

edited May 23 '17 at 11:48

Community

1
1

answered Sep 30 '16 at 19:43

Andreas

4,937
2
25
35

JohnLBevan · Answer 3 · 2016-09-30T19:54:59.920

This should do it:

insert into Program (user, program, installed, department, whenInstalled)
select user, program, installed, department, whenInstalled
from 
(
    select User
    , 'MyMostAwesomeProgram' program
    , 0 installed
    , department
    , null whenInstalled
    , row_number() over (partition by user order by whenInstalled desc, id desc) r
from Program
) p
where p.r = 1

The interesting bit is the row_number() over (partition by user order by whenInstalled desc, id desc) r.

This says to return a column, r, which holds values 1..n for each user, counting up according to the order by clause (i.e. starting with the most recent whenInstalled and working backwards).

I also included the id field in the order by clause in case there were two installs for the same user on the same date; in such a case the most recently added (the one with the higher id is used first).

We then put this in a subquery, and select only the first record; thus we have 1 record per user, and it's the most recent.

The only values we use from this record are the user and department fields; all else is defined per your defaults.

ps. good blog on this & similar functions here if you'd like to read up on it. http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/ — JohnLBevan, Sep 30 '16 at 19:56
Thanks a lot. Your query helped me a lot to get it done finally. Now I understand partition vs vs group by, those short aliases and at the end I used some rank function becouse your query resulted on only one row returned. But with rank it works perfectly :]. Anyway for those who might google this: — Radek Piekný, Oct 01 '16 at 12:57

score 0 · Answer 4 · answered Oct 01 '16 at 13:10

So I am gonna answer this for some other people who might google this:

To get a records from a table to another table you need to use Select into statement
I like using With XXX as ( some select) to specify, say, "virtual" table with which you can work during the query
As JohnLBevan meantioned a very useful function Row_number, over, partition by and what i missed is a rank

So once you read on these you should be able to understand how to do what I wanted to do.

SQL - Insert multiple records from select

4 Answers4