how to generate query to select/update records

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)

how to generate query to select/update records

Author

Topic

sarojanand
Starting Member

1 Post

Posted - 2015-03-07 : 20:58:07

I have a task where the requirement is as follows:

create a procedure which accepts tableName, columnName, Key as parameter.

based on these parameters, select records from the said tableName where columnName has matching Key.

get data from history table and update matching rows in the tableName table either by delete the old record and insert a new row in table or just update each column with new value. There are more than 100 tables in db and each having different schema. If there are multiple records, update each of them.

So far I am able to generate the sql dynamically and record with query like:

declare @sqlQuery varchar(max), @SQLString varchar(max);
SET @sqlQuery = '(SELECT * FROM [AdventureWorks2012].[HumanResources].[Employee] WHERE [BusinessEntityID] = 2)';
SET @SQLString = N'
SELECT * INTO ##temptbl
FROM OPENROWSET (''SQLOLEDB'',''Server=ZZZZ-PC;Trusted_Connection=yes;'',''' + @sqlQuery + ''')'

EXEC (@SQLString)
select * from ##temptbl;

drop table ##temptbl;
Its returning me the correct record.

Now I am stuck with how do I know how many columns are there in this table and how to update each of them. Each tableName will return different set of columns.

Also please let me know this is the best approach to solve this issue.

- Saroj

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-08 : 09:53:35

You can use system tables for that:

e.g.


select t.name, c.name from sys.tables t
join sys.columns c
on t.object_id= c.object_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources