CODEWITHSUNDEEP
sqlsql-serverlookup-tables
barry
barry

Reputation: 4147

Populate table using lookup tables

I'm trying to extract data from a single MS SQL Server table which uses nvarchar(MAX) as the data type for a couple of columns and insert it into a table which uses ints as the data type for the coresponding nvarchar(MAX) data. I have a couple of tables (lookup tables?) to provide the int values.

The tables have the following designs:

MobileClientTable (the source table)

enter image description here

LookupWeather

enter image description here

LookupArea

enter image description here

DestinationTable

enter image description here

Some sample data:

MobileClientTable

enter image description here

LookupWeather

enter image description here

LookupArea

enter image description here

So what I'd like are rows in the Destination table like this:

ID   Area   Weather   Details
1    4      1         None

where 4 and 1 for Area and Weather have been obtained from the lookup tables.

My SQL is a tad rusty, but I've managed to get as far as this SQL statement:

USE [ETLTest]
GO

INSERT INTO DestinationTable(ID, Area, Weather, Details) VALUES 
    (
        (SELECT ID FROM dbo.MobileClientTable),
        (SELECT ID FROM dbo.LookupArea WHERE Area = dbo.MobileClientTable.Area),
        (SELECT ID FROM dbo.LookupWeather WHERE Weather = dbo.MobileClientTable.Weather,
        (SELECT AnyOtherDetails FROM dbo.MobileClientTable)
    );

Of course, the syntax is incorrect. I get the following errors:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

Where am I going wrong?

Upvotes: 2

Views: 1301

Answers (2)

Simon
Simon

Reputation: 1081

INSERT INTO DestinationTable(ID, Area, Weather, Details)
    SELECT a.ID, b.Area, c.Weather, d.AnyOtherDetails 
    FROM dbo.MobileClientTable a
    JOIN dbo.LookupArea b ON a.Area=b.Area
    JOIN dbo.LookupWeather c ON c.Weather = a.Weather

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

values isn't required as a select is being used.

Join the tables to insert desired values into the respective columns.

INSERT INTO DestinationTable(ID, Area, Weather, Details)
SELECT mc.ID , a.id, w.id, mc.anyotherdetails
FROM dbo.MobileClientTable mc
join dbo.LookupWeather w on w.Weather = mc.Weather
join dbo.LookupArea a on a.Area = mc.Area

Upvotes: 5

Related Questions