动态列名称的sql更新 [英] sql update with dynamic column names

问题描述

编辑：为简单起见，数据库名称已修改

Database names have been modified for simplicity

我正在尝试获取一些动态sql来更新 static 副本一些关键的生产表到另一个数据库（sql2008r2）。因为我们的生产数据库几乎每天都在更新，所以此处的目的是允许在一定时期内一致地分发数据（从静态数据库）。

I'm trying to get some dynamic sql in place to update static copies of some key production tables into another database (sql2008r2). 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.

我是使用 CURSOR 遍历一个表，该表包含要复制到静态数据库中的对象。
prod 表不会经常更改，但是我想对此做些未来证明（如果可能！）并提取列名称从 INFORMATION_SCHEMA.COLUMNS 中获取每个对象（而不是使用 SELECT * FROM ... ）

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 ...)

• 1）从我在其他帖子中所读的内容， EXEC（） 似乎有限制，所以我认为我需要使用 EXEC sp_executesql ，但是我遇到了一些麻烦

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

2）作为补充，如果可能的话，我也想排除某些特定表的列（结构在静态数据库）

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)

这是我到目前为止的内容。
执行时， @colnames 返回NULL，因此 @sql 返回NULL ...

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?
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。我已经阅读了有关SQL注入的信息，但是由于这是内部管理任务，我想我在这里很安全！

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.

在此先感谢。

推荐答案

在针对 information_schema 的查询中，您混合使用了SQL和动态SQL。另外，在where子句中也不需要 QUOTENAME ，因为SQL Server存储 column_name ，实际上它根本阻止了匹配。 ，而不是元数据中的 [列名] 。最后，我将其更改为 sys.columns ，因为这是我们应该在SQL Server中导出元数据。尝试：

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 we should be deriving metadata in SQL Server. 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, '');

这篇关于动态列名称的sql更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！