insert multiple records into multiple columns of a table from many tables

Question

I want to insert multiple records into multiple columns of a table from many tables. Below is my query, but I just get to insert the records into the first column. The other columns populate with nulls. Can you let me know what am I doing wrong?

INSERT INTO [dbo].[dim_one_staging] ([Parent], [Child], [Child_Alias], [Operator])
   SELECT
      p.[Parent], c.[Child], a.[Child_Alias], o.[Child_Operator]
   FROM
      [dbo].[Staging_Parent] AS p
   INNER JOIN 
      [dbo].[Staging_Child] AS c ON p.[id] = c.[id]
   INNER JOIN 
      [dbo].[Staging_Child_Alias] AS a ON c.[id] = a.[id]
   INNER JOIN 
      [dbo].[Staging_Operator] AS o ON a.[id] = o.[id]

Answer 1

Your query is syntactically correct. That doesn't mean it does what you want it to do.

It could be that you have no values in

,c.[Child]
,a.[Child_Alias]
,o.[Child_Operator]

for the records that meet the rest of the query conditions and thus null is the correct value.

It could be that you have no valaues in the join tables for those fields but you should have values, in which case there is a bug in the way the data in being entered into these tables.

Or it could be that you are trying to get values froma table where the value is not required and put them into a table where it is and thus need to use coalesce (or default values) to define what should go in there if the value is null.

Yet another possibility is that there is trigger on the table that is nulling the values out.

Only you can detrmine what the problem is from teh data structure you have and the meaning attached to the data. I don't know how to fix your problem because I don't actually understand your datamodel as far as meaning (as opposed structure.)