10997

sql update with dynamic column names

Question:

EDIT: Database names have been modified for simplicity

I'm trying to get some dynamic sql in place to update <em>static</em> copies of some key production tables into another database <strong>(sql2008r2)</strong>. The aim here is to allow consistent dissemination of data (from the 'static' database) for a certain period of time as our production databases are updated almost daily.

I am using a CURSOR to loop through a table that contains the objects that are to be copied into the 'static' database. The prod tables don't change that frequently, but I'd like to make this somewhat "future proof" (if possible!) and extract the columns names from INFORMATION_SCHEMA.COLUMNS for each object (instead of using SELECT * FROM ...)

<ul><li>

1) From what I have read in other posts, <strong>EXEC()</strong> seems limiting, so I believe that I'll need to use <strong>EXEC sp_executesql</strong> but I'm having a little trouble getting my head around it all.

</li> <li>

2) As an added extra, if at all possible, i'd also like to exclude some columns for particular tables (structures vary slightly in the 'static' database)

</li> </ul>

here's what i have so far. when executed, @colnames returns NULL and therefore @sql returns NULL...

could someone guide me to where i might find a solution?<br /> any advice or help with this code is much appreciated.

CREATE PROCEDURE sp_UpdateRefTables @debug bit = 0 AS declare @proddbname varchar(50), @schemaname varchar(50), @objname varchar(150), @wherecond varchar(150), @colnames varchar(max), @sql varchar(max), @CRLF varchar(2) set @wherecond = NULL; set @CRLF = CHAR(10) + CHAR(13); declare ObjectCursor cursor for select databasename,schemaname,objectname from Prod.dbo.ObjectsToUpdate OPEN ObjectCursor ; FETCH NEXT FROM ObjectCursor INTO @proddbname,@schemaname,@objname ; while @@FETCH_STATUS=0 begin if @objname = 'TableXx' set @wherecond = ' AND COLUMN_NAME != ''ExcludeCol1''' if @objname = 'TableYy' set @wherecond = ' AND COLUMN_NAME != ''ExcludeCol2''' --extract column names for current object select @colnames = coalesce(@colnames + ',', '') + QUOTENAME(column_name) from Prod.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = + QUOTENAME(@objname,'') + isnull(@wherecond,'') if @debug=1 PRINT '@colnames= ' + isnull(@colnames,'null') --replace all data for @objname --@proddbname is used as schema name in Static database SELECT @sql = 'TRUNCATE TABLE ' + @proddbname + '.' + @objname + '; ' + @CRLF SELECT @sql = @sql + 'INSERT INTO ' + @proddbname + '.' + @objname + ' ' + @CRLF SELECT @sql = @sql + 'SELECT ' + @colnames + ' FROM ' + @proddbname + '.' + @schemaname + '.' + @objname + '; ' if @debug=1 PRINT '@sql= ' + isnull(@sql,'null') EXEC sp_executesql @sql FETCH NEXT FROM ObjectCursor INTO @proddbname,@schemaname,@objname ; end CLOSE ObjectCursor ; DEALLOCATE ObjectCursor ;

P.S. i have read about sql injection, but as this is an internal admin task, i'm guessing i'm safe here!? any advice on this is also appreciated.

many thanks in advance.

Answer1:

You have a mix of SQL and dynamic SQL in your query against information_schema. Also QUOTENAME isn't necessary in the where clause and will actually prevent a match at all, since SQL Server stores column_name, not [column_name], in the metadata. Finally, I'm going to change it to sys.columns since this is the way <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx" rel="nofollow">we should be deriving metadata in SQL Server</a>. Try:

SELECT @colnames += ',' + name FROM Prod.sys.columns WHERE OBJECT_NAME([object_id]) = @objname AND name <> CASE WHEN @objname = 'TableXx' THEN 'ExcludeCol1' ELSE '' END AND name <> CASE WHEN @objname = 'TableYy' THEN 'ExcludeCol2' ELSE '' END; SET @colnames = STUFF(@colnames, 1, 1, '');

Recommend

  • set ruby hash element value by array of keys
  • Hide UIView starting from Bottom on Scroll
  • Getting rollback in creating shortcuts on installation of node js in windows 7
  • LDA: Why sampling for inference of a new document?
  • ASP.NET MVC C#: Object reference errors when going to view record details
  • How to get database credentials into a c# application without committing it to source code?
  • How can I include the Ivy dependency and none of its dependencies?
  • Struct pointer casts
  • Can you use DataSet and DataTables in a Portable Class Library
  • Is there a way to call library thread-local init/cleanup on thread creation/destruction?
  • Accessing the variables from a PHP Anonymous Function
  • Classic ASP URL Rewriting
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • Thread synchronization with syncwarp
  • conditions for accessors in Coldfusion ORM
  • Efficient User-Agent Regex to find Safari in Python
  • How can I run DataNucleus Bytecode Enhancer from SBT?
  • Jquery Knockout: ko.computed() vs classic function?
  • jQuery: add elements until a particular height is reached
  • Simple linked list-C
  • Tell Git to stop prompting me for conflicts when none really exist?
  • msbuild create itemgroup from property group
  • Reduction and collapse clauses in OMP have some confusing points
  • How to define and use opencv mat of user type
  • Jquery UI tool tip close icon
  • Projection media query: browser support and workarounds?
  • Display issues when we change from one jquery mobile page to another in firefox
  • Can Jackson SerializationFeature be overridden per field or class?
  • Java applet as stand-alone Windows application?
  • Cassandra Data Model
  • ActionScript 2 vs ActionScript 3 performance
  • Large data - storage and query
  • R: gsub and capture
  • AT Commands to Send SMS not working in Windows 8.1
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do I configure my settings file to work with unit tests?
  • Getting Messege Twice Using IMvxMessenger
  • Is it possible to post an object from jquery to bottle.py?
  • How to Embed XSL into XML
  • Python/Django TangoWithDjango Models and Databases