Compositing ordered SQL tables without Joins

Question

I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology.

Background

I have a handful of NF3 tables, and NF1 source data. Here's a slice of them:

SourceData : (ProjectID, Manager, City, Status, Cost)

Destination Data:

Projects (ProjectID, ManagerID, CityID, StatusID, Cost)
Managers (ManagerID, ManagerName, [...])
Cities (CityID, CityName, [...])
Status (StatusID, StatusName, [...])

Projects_View (ProjectID, Manager, City, Status, Cost)

Desired Outcome

For a given row in the source data, I want to insert that into Projects, while also inserting into the FK tables Managers, Cities and Status as required. The method I'm using is to first Merge into all of the FK tables, and then insert into Projects once the FK constraint is met.

The part I don't like in this methodology is the joining on varchar columns to get IDs - especially since I just merged against those IDs earlier in the transaction. For example:

INSERT INTO Projects
  sd.ProjectID
, m.ManagerID
, c.CityID
, s.StatusID
, sd.Cost
FROM SourceData AS sd
LEFT JOIN Managers AS m ON sd.ManagerName = m.ManagerName
LEFT JOIN Cities   AS c ON sd.CityName    = c.CityName
LEFT JOIN Status   AS s ON sd.StatusName  = s.StatusName

Obviously, I could create indices on those ...Name columns, but it feels backwards to be using the value to retrieve the key. First question - Is this reverse index good practice given the use case?

Method 1 - Output identity pairs

My first pass as an alternative is to use the Output clause of my merges to capture the identity values into a temp table/table variable, which looks like:

DECLARE @ManagerOutput TABLE (ProjectID int, ManagerID int);
MERGE INTO [Managers] AS tgt
    USING (SELECT ProjectID, ManagerName FROM SourceData) as src
    ON tgt.ManagerName = src.ManagerName
    WHEN NOT MATCHED THEN  
        INSERT (ManagerName)  
        VALUES (src.ManagerName)
    WHEN MATCHED THEN
        UPDATE SET tgt.ManagerName = tgt.ManagerName
    OUTPUT src.ProjectID, inserted.ManagerID INTO @ManagerOutput;

[...]

INSERT INTO Projects
      sd.ProjectID
    , m.ManagerID
    [...]
    FROM SourceData AS sd
    LEFT JOIN @ManagerOutput AS m ON sd.ProjectID = m.ManagerID
    LEFT JOIN [...]

It does get me to a nicer int->int join, but there's not only overheads in creating a table variable, but also an inefficiency in the fake update within the Merge to force inclusion of existing IDs in the inserted pseudo-table. I think the litmus test for this would be to execute both methods against a large dataset and measure them against one another.

One Weird Trick?

It did get me thinking about whether it's possible to combine table data in unorthodox ways though. Using the Merge statement in Method 1 above, here's my plan:

Method 2 - Dumb Join

• Order DataSource by ProjectID
• Order ManagerOutput by ProjectID
• Combine the two with no logic
• Insert into Projects

Second Question - Is that possible? - I know that I could create a couple of CTEs with ROW_NUMBER() OVER columns AND join on the row numbers, but I'm genuinely looking for a completely dumb SELECT statement that functions like a UNION ALL except instead of appending two tables end-to-end, it appends them side-by-side.

Method 3 - Dumb update

The last thought I had was to avoid joins (almost) altogether in favour of inserting/updating over the top of existing rows. Here's what I mean by that:

DECLARE 
@ManagerOutput TABLE (ProjectID int, ManagerID int),
@StatusOutput TABLE (ProjectID int, StatusID int),
[...]
@IDs TABLE ( 
        ProjectID   int
    ,   ManagerID   NULL int
    ,   StatusID    NULL int
    [...]
)

INSERT INTO @IDs (ProjectID, ManagerID)
SELECT * FROM @ProjectOutput

With the idea being that if all tables are ordered by ProjectID, then taking all rows of @StatusOutput.StatusID and "pasting" them over the existing NULL values in @IDs would correlate exactly to ProjectID.

But obviously, this is not a very SQL way of thinking. I know I could accomplish this by merging or updating with a join, or even something really silly like deleting the contents of @IDs, outputting the deleted rows alongside @StatusOutput and then recursively inserting them back into @IDs. None of which are likely to be a better solution than just doing the joins in the first place.

Third Question - Is it possible to blindly insert/update an entire column with no underlying logic?

I suspect the answers to both Two and Three are - "No, absolutely not. This functionality does not exist in order to protect your data from you." But I'd love to hear from anyone who might have explored these possibilities. In the meantime, I'll keep prototyping and comparing the methods that are actually possible.

Thanks!

Answer 1

My first choice would be your very first option (option 0?). It's very clear and obvious what you are doing and how.

If there are problems with creating an index on your master data's names you can

a) Ignore the inefficiency and do the job during quiet or otherwise time-acceptable period. Depends on how much data & how long.

b) Go via a temporary or staging table where you create the index on name.

Unless you've established a burning need, all your other options seem to be over-complex contrivances.