SQL Insert with value from different table

Question

I have 2 tables storing information. For example: Table 1 contains persons:

ID         NAME         CITY

1          BOB          1
2          JANE         1
3          FRED         2

The CITY is a id to a different table:

ID         NAME

1          Amsterdam
2          London

The problem is that i want to insert data that i receive in the format:

ID         NAME         CITY

1          PETER        Amsterdam
2          KEES         London
3          FRED         London

Given that the list of Cities is complete (i never receive a city that is not in my list) how can i insert the (new/received from outside)persons into the table with the right ID for the city?

Should i replace them before I try to insert them, or is there a performance friendly (i might have to insert thousands of lines at one) way to make the SQL do this for me?

The SQL server i'm using is Microsoft SQL Server 2012

@GordonLinoff im receiving this data from a external application ;) — Flying Dutch Boy, Apr 25 '16 at 11:43

score 2 · Accepted Answer · answered Apr 25 '16 at 11:42

First, load the data to be inserted into a table.

Then, you can just use a join:

insert into persons(id, name, city)
     select st.id, st.name, c.d
     from #StagingTable st left join
          cities c
          on st.city = c.name;

Note: The persons.id should probably be an identity column so it wouldn't be necessary to insert it.

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

insert into persons (ID,NAME,CITY) //you dont need to include ID if it is auto increment
values 
(1,'BOB',(select Name from city where ID=1)) //another select query is getting Name from city table

if you want to add 1000 rows at a time that'd be great if you use stored procedure like this link

edited May 23 '17 at 12:09

Community

1
1

answered Apr 25 '16 at 11:45

M.Nabeel

1,066
7
19

if i insert a thousand a records at a time, wont it result in the sub-query running a thousand times? – Flying Dutch Boy Apr 25 '16 at 11:47
that'd be great if you write stored procedure for that it is way efficient thn inline query – M.Nabeel Apr 25 '16 at 11:48
Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Apr 25 '16 at 13:46

SQL Insert with value from different table

2 Answers2