0
Posted on Wednesday, January 07, 2015 by 醉·醉·鱼 and labeled under
还是第一次遇到column LIKE column的情况。

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

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

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


0
Posted on Tuesday, January 06, 2015 by 醉·醉·鱼 and labeled under
做数据修复的时候,有时候需要动态生成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
0
Posted on Tuesday, January 06, 2015 by 醉·醉·鱼 and labeled under ,

实例如下


0
Posted on Monday, January 05, 2015 by 醉·醉·鱼 and labeled under
在寻找一些关于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