一段更新数据的脚本

把他贴出来不是因为它有多么高深，只是因为它写的足够规范，可以参考一下

 

前段代码是由于我们的开发环境的特殊性，即本地的数据库都是数据库名+test,而在dev数据库上是不带test后缀的：

 

DECLARE @maDbName SYSNAME

//DB_NAME()取得是当前的数据库名称
IF (DB_NAME() LIKE N'%Test')
BEGIN
 SET @maDbName = N'MemberArchiveTest'
END
ELSE
BEGIN
 SET @maDbName = N'MemberArchive'
END

IF (NOT EXISTS (SELECT 1 FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.Tr_Role', N'U')
   AND [name] = N'MembershipNumber'))
BEGIN
 ALTER TABLE dbo.Tr_Role
  ADD MembershipNumber NVARCHAR(255) NULL
END

IF (EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE [name] = @maDbName))
BEGIN
 DECLARE @sql NVARCHAR(MAX), @isMigrationDone BIT
 SET @sql = N'
  IF (EXISTS (SELECT 1 FROM ' + @maDbName + N'.sys.columns WHERE [object_id] = OBJECT_ID(N''' + @maDbName + N'.dbo.Tm_Member'', N''U'')
     AND [name] = N''MembershipNumber''))
  BEGIN
   SET @isMigrationDone = 0
  END
  ELSE
  BEGIN
   SET @isMigrationDone = 1
  END
  '
 EXEC sp_executesql @sql, N'@isMigrationDone BIT OUTPUT', @isMigrationDone = @isMigrationDone OUTPUT
 IF (@isMigrationDone = 0)
 BEGIN
  SET @sql = N'
   UPDATE r
    SET MembershipNumber = ma.MembershipNumber
    FROM dbo.Tr_Role r
     INNER JOIN ' + @maDbName + N'.dbo.Tm_Member ma
      ON r.PersonId = ma.PersonId AND r.OrganisationId = ma.ClubId
       AND r.IsPassive = ma.IsPassive
    WHERE r.RoleTypeId = 1
   '
  EXEC sp_executesql @sql
  
  SET @sql = N'
   USE ' + @maDbName + N'
   ALTER TABLE dbo.Tm_Member DROP COLUMN MembershipNumber
   '
  EXEC sp_executesql @sql
 END
END