HoneyHoney

还是第一次遇到column LIKE column的情况。

背景是这样。需要找到当前数据库里面所有column，但想排除某些特定的column，而且希望这个是能够通过配置即可自动实现而不需要改query。关键就在创建这个配置表，并且LIKE的使用。

首先创建一个#tmp的临时表，写入配置。此后将临时表的value写到LIKE之后。

想了一下，用到这种方法的情况挺少的，蛮有意思。

做数据修复的时候，有时候需要动态生成rollback script。下面就是用AdventureWorks2012做个例子自动生成rollback script。

USE AdventureWorks2012;

DECLARE @personId int = 1

CREATE TABLE #tmp_person(
 BusinessEntityID int,
 PersonType nchar(2),
 NameStyle bit,
 Title nvarchar(8),
 FirstName nvarchar(50),
 MiddleName nvarchar(50),
 LastName nvarchar(50),
 Suffix nvarchar(10),
 EmailPromotion int,
 AdditionalContactInfo xml,
 Demographics xml,
 rowguid uniqueidentifier,
 ModifiedDate datetime);

CREATE TABLE #tmp_address(
 AddressID int,
 AddressLine1 nvarchar(60),
 AddressLine2 nvarchar(60),
 City nvarchar(30),
 StateProvinceID int,
 PostalCode nvarchar(15),
 SpatialLocation geography,
 rowguid uniqueidentifier,
 ModifiedDate datetime);


INSERT INTO #tmp_person
SELECT TOP 1 *
FROM Person.Person
WHERE BusinessEntityID = @personId

--delete data
--DELETE FROM Person.Person WHERE BusinessEntityID = @personId


PRINT 'ROLLBACK SCRIPT'
PRINT '----------------------------------------'

SET NOCOUNT ON

CREATE TABLE #tables (id int identity PRIMARY KEY, table_name varchar(50), tmp_table_name varchar(50))

--tables to insert the data back
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Person', '#tmp_person')
INSERT INTO #tables(table_name, tmp_table_name) VALUES ('Person.Address', '#tmp_address')

DECLARE @columns NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @tablename VARCHAR(50),
  @tmptablename VARCHAR(50),
  @rowcount int

------------------- AdventureWorks2012 -------------------
PRINT 'USE AdventureWorks2012;'

DECLARE table_cursor CURSOR FAST_FORWARD LOCAL
FOR
SELECT table_name, tmp_table_name
FROM #tables
ORDER BY id

OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @tablename, @tmptablename

WHILE @@FETCH_STATUS = 0
 BEGIN

 --Check if data exists in temp table. Skip if no data
 SET @sql = N'SELECT 1 FROM ' + @tmptablename
 EXEC(@sql)

 SET @rowcount = @@rowcount

 IF @rowcount <> 0
 BEGIN
  -- get columns names to be inserted
  SELECT @columns = STUFF(
  (SELECT '+'''''', '''''' + ISNULL(CAST(' + name + ' AS varchar(2000)), ''NULL'')' FROM sys.columns c
   WHERE object_id = object_id(@tablename)
   ORDER BY c.column_id
   FOR XML PATH('')
  ), 1, 12, '')


  -- format INSERT script
  SET @sql = N'SELECT @sqlout = STUFF(
          (SELECT N''INSERT INTO ' + @tablename + ' VALUES ('''''' + ' + @columns + N' + N''''''); ''
          FROM ' + @tmptablename +'
          FOR XML PATH('''')
          ), 1, 0, '''')'

  EXEC sys.sp_executesql @sql, N'@sqlout NVARCHAR(MAX) OUTPUT',  @sqlout = @sql OUTPUT

  SET @sql = REPLACE(@sql, '''NULL''', 'NULL')
  SET @sql = REPLACE(@sql, 'INSERT INTO', CHAR(10)+'INSERT INTO')

  PRINT @sql

 END

 FETCH NEXT FROM table_cursor
 INTO @tablename, @tmptablename

 END

CLOSE table_cursor
DEALLOCATE table_cursor

实例如下

在寻找一些关于SQL SERVER CURSOR FAST_FORWARD的文章，发现这么一个案例。都知道用游标可能会导致运行速度很慢，但又如何去处理呢？在这个案例里面，他需要处理近60000条记录，每条记录又会衍生更多的操作等等。在添加上FAST_FORWARD之后情况，情况就好了很多。

http://mitchelsellers.com/blogs/2011/09/23/sql-server-forward_only-cursor-for-performance.aspx

在这里，MSDN更加详细地解释了FAST_FORWARD.
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx