Insert values from another table

Question

I am trying to insert into a field using the input from another table field. Although this is entirely feasible for many database engine,

I have the following fields,

New Table Schema

• Name
• Industryid(int)
• Locationid(int)

But the actual records like,

• xxxx
• TEXT (varchar)
• TEXT(Varchar)

But I want these two Industryid and Locationid by id's from another table Industries, Locations.

I have another 2 tables for

Industries

• Id
• Name

Locations

• Id
• Name

My Query is,

select'insert into organizations(name,industryid,locationid)
values
('''+
Nameofthecompany+''','+
Isnull(industrytype,'Null')+','+
ISNULL(Location,'Null')+')' 
from Organization`

Result

insert into organizations(name,industryid,locationid) 
values 
('Swamy',Telcom,Chennai)

Expected Result

insert into organizations(name,industryid,locationid)
values
('Swamy',12,150)

Answer 1

If your current/actual records are already in a table named Organization as shown in your query, you can use this approach to populate your new Organizations table:

INSERT INTO Organizations( name, IndustryId, LocationId )
SELECT o.Nameofthecompany, i.Id, l.Id
FROM Organization AS o
LEFT JOIN Industries AS i ON o.industrytype = i.Name
LEFT JOIN Location AS l ON o.Location = l.Name

This takes the existing names, joins to your reference tables (Industries and Locations) and retrieves the Id values for the corresponding text values.

The LEFT JOIN will bring back all of the names from Organization, but not require them to have a matching Industry and Location in the reference tables.

Answer 2

You can do something like this:

SELECT Col1, Col2
INTO TestTable
FROM OriginalTable
WHERE Conditions

Notice that SELECT INTO only works if the table specified in the INTO clause does not exist

Or:

INSERT INTO TestTable (Col1, Col2)
SELECT Col1, Col2
FROM OriginalTable
WHERE Conditions