Populate table where columns match | TSQL

Question

This is my main table:

select Name, Age, Race, from Person

I create a temp table based on what is available in my XML file, thus the temp table might have all of the fields in my table Person or just 1 or just 2 in a mix order.

Select * from #MyTempTable = Mike, 44

or it can be

Select * from #MyTempTable = Race, Mike

Is it possible to populate my Person table with what ever values I have in my temp table?

So if the #MyTempTable have a column Age then populate the Person table with just the Age value into column Age in table Person

Answer 1

SQL Server solution:

You could try one of these solutions:

IF EXISTS(SELECT * FROM tempdb.sys.columns c 
    WHERE c.object_id=OBJECT_ID(N'tempdb..#MyTempTable' AND c.name='Age')
BEGIN
    INSERT INTO #MyTempTable(Name,Age)
    SELECT ... FROM ... CROSS APPLY XmlCol.nodes('/...') a(b)
END
ELSE IF EXISTS(SELECT * FROM tempdb.sys.columns c 
    WHERE c.object_id=OBJECT_ID(N'tempdb..#MyTempTable' AND c.name='Race')
BEGIN
    INSERT INTO #MyTempTable(Name,Race)
    SELECT ... FROM ... CROSS APPLY XmlCol.nodes('/...') a(b)   
END

or

INSERT INTO #MyTempTable(Name,Age,Race)
SELECT ... AS Name, 
    a.b.value('(@Age)[1]','TINYINT') AS Age
    a.b.value('(@Race)[1]','NVARCHAR(50)') AS Race, ...
    CASE WHEN <condition for Race column> THEN value ELSE NULL AS Race   
FROM ... CROSS APPLY XmlCol.nodes('/...') a(b)

Note 1: Because reading data from sys.columns system view requires special rights (see link) the simplest solution is the second solution.

Note 2: Starting from SQL Server 2008 you could use SPARSE columns to reduce the storage size for those columns that have columns with many NULLs.

Answer 2

I would suggest turning the XML into a query to create a temp table (SQL Server shred XML to temp table). Then you can have separate update statements for Age and Race, for example:

UPDATE Person
SET Age = Imported.Age
FROM Person
   INNER JOIN Imported ON Imported.Name = Person.Name
WHERE Imported.Age IS NOT NULL

And so on.