TSQL根据数据类型更新每个列名的stmt

Question

对于SSIS，我需要创建一个TSQL更新工作流，以便在导入错误的情况下覆盖当前表记录。

我已经设置了整个SSIS过程，但我缺少SQL更新语句。

因此，如果在导入过程中出现问题，表中的当前记录(所有行)应该使用短消息更新-例如"错误数据库导入"。

由于我有多个表要处理，我也得到不同的列名和数据类型。
我将使用这个stmt来获取列名

SELECT COLUMN_NAME , * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'

但是我如何为字符类型设置字符串消息，为数字和日期类型设置NULL。使用CASE stmt?

在伪代码中，它可能只是通过列循环:如果column_name是data_type "char" then…

我还需要忽略每个表的前4列，这样我就不会覆盖ID, Date等。

如果你能帮我设置一个静态测试更新stmt，我相信我能把它转移到我的SSIS项目。

谢谢。

Answer 1

听起来你在找这样的东西:

SELECT
  CASE DATA_TYPE
    WHEN 'int' THEN NULL
    WHEN 'varchar' THEN 'STRING MSG GOES HERE'
  END,
COLUMN_NAME , * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
  AND ORDINAL_POSITION > 4

SQL Fiddle Demo

您可以根据需要在CASE语句中添加任意数量的WHEN子句。此外，您希望使用ORDINAL_POSITION列来排除前4列。

如果您需要使用这些信息来创建UPDATE语句，那么您需要使用动态SQL来执行此操作。

EDIT—Dynamic SQL:

create procedure updateMyTable
as 
begin
  declare @sql varchar(max)
  SELECT @sql = 'UPDATE myTable SET ' + 
  STUFF(
    (SELECT ', ' + COLUMN_NAME + ' = ' +
    CASE DATA_TYPE
      WHEN 'int' THEN 'NULL'
      WHEN 'varchar' THEN '''STRING MSG GOES HERE'''
    END 
   FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTable'
   for xml path(''))
   ,1,1,'')
  exec(@sql)
end

SQL Fiddle Demo