Update table from table possible pivot

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update table from table possible pivot

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-01-20 : 09:31:53

I have an inherited database and in it is a table, tblConstants.
It has almost 100 columns and only one row.
Field example names are CompanyName, CompanyAddr1, CompanyAddr2

This table needs to be fixed, so I created another table, Contants.
It has fields ID, Name, Value, Active, UpdatedBy, UpdatedDate

After creating the new tale, filling one field is easy:


INSERT INTO dbo.Constants ( Name ) SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblConstants' 
AND COLUMN_NAME <> 'ROWid'

But this leaves the actual values, so a table to table was attempted:

UPDATE t2 
SET t2.Value = t1.?? 
FROM Constants t2
INNER JOIN INFORMATION_SCHEMA.COLUMNS t1 ON t2.Name = t1.COLUMN_NAME
WHERE t1.TABLE_NAME = 'tblConstants' 
AND t1.COLUMN_NAME <> 'ROWid'

As can be seen where the ?? is, that's where I'm stuck.
Or maybe this needs some sort of pivot to work.

Any input would be helpful.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 07:16:06

it should be this


UPDATE t2 
SET t2.Value = t1.Val
FROM Constants t2
INNER JOIN (SELECT Col,Val
            FROM tblConstants
            UNPIVOT(Val FOR Col IN (COl1,Col2,..Col100))u
            )t1
ON t1.Col = t2.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-01-22 : 08:30:51

Thanks for the reply. It looks like your suggestion would work but after I was working with an UNPIVOT, I discovered that all the fields need to have the same data type, which all fields do not.
It would be more work converting each column in the query for it to work than it was creating separate insert statements.

And this way I have the data for the constants in a separate sql file to re-create the table or update or insert new values. I haven't had time yet to create a GUI for this table.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 08:37:47

quote:
Originally posted by Zath

Thanks for the reply. It looks like your suggestion would work but after I was working with an UNPIVOT, I discovered that all the fields need to have the same data type, which all fields do not.
It would be more work converting each column in the query for it to work than it was creating separate insert statements.

And this way I have the data for the constants in a separate sql file to re-create the table or update or insert new values. I haven't had time yet to create a GUI for this table.

Thanks.

you can always generate code to convert all columns to same data type (varchar/nvarchar) and then apply UNPIVOT on them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources