I'm currently exploring some weird ways of doing things and was wondering if anyone has any experience with the following methodology.
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)
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.