The real problem with being a MidnightDBA is midnight blogging. It's a syndrome in which you find yourself with this idea, this great and very clever idea, that you want to share with all of blogdom. So you – let's be honest here – *I* start to write up my idea, and it turns out to be something blindingly, astoundingly ordinary and obvious.
I lie. The REAL real problem with being a MidnightDBA is getting all of that, and then blogging about it anyway.
So recently I was coding for an unfamiliar system, and discovered this setup: there's a master configuration table that holds settings for other units in the database. Any time a new unit is inserted, you also have to insert settings to the config table, using the master config table as a template. Eh? An example, you say? Certainly!
Master configs:
-
Setting 1, blue
-
Setting 2, medium
-
Setting 3, round
Insert Unit 1 into the database. Use master configs as a template to insert Unit 1 local configs:
-
Unit 1, Setting 1, blue
-
Unit 1, Setting 2, medium
-
Unit 1, Setting 3, round
You know, only slightly more complicated. So if we have a set of units we need to insert, and a set of configs for each unit, we'll need a statement like this:
SELECT DISTINCT UnitID
INTO #tempUnits
FROM Unit
SELECT
C.OrgID
,C.ConfigID
,C.DefaultValue StartValue
,U.UnitID ID
,C.TypeID
,GETDATE() ModifiedDate
FROM Config C
JOIN #tempUnits U ON 1=1
The part I thought was super clever was that whole inner join on nothing thing. As it turns out, that is kinda nifty, but it's also something very simple: a CROSS JOIN. It makes sense: a cross join gives you a set of rows from Table2 for each row in Table1. An INNER JOIN gives you all the rows from Table1 that match Table2, on the join condition. Since there's no join condition here, it just ..well let's not get repetitious…it just gives you a CROSS JOIN.
Wasn't that fun? I gotta get some sleep…