MSSQL批量循环创建新表：根据数据库A中的表A字段的记录值为新表名并创建新表到数据库B中

最新推荐文章于 2019-05-21 11:27:00 发布

AMinfo

最新推荐文章于 2019-05-21 11:27:00 发布

阅读量2k

点赞数

分类专栏： MSSQL

本文链接：https://blog.csdn.net/AMinfo/article/details/24065969

版权

MSSQL 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

MSSQL批量循环创建新表：根据数据库A中的表A字段的记录值为新表名并创建新表到数据库B中

http://blog.csdn.net/aminfo/article/details/24065969

/****** 从数据库A的表A获得记录  ******/
declare cur_test cursor for SELECT Field1,Field2,Field3 FROM [数据库A].[dbo].[表A] order by Field1
declare @Field1 nvarchar(50), @Field2 nvarchar(50), @Field3 nvarchar(50)
declare @NewTable nvarchar(100)
declare @sql nvarchar(max)

open cur_test
fetch next from cur_test into @Field1,@Field2,@Field3
while  @@fetch_status=0
begin
print @Field1 + '-' + @Field2 + '-' + @Field3
/****** 待创建的新表名由获得的记录值组成  ******/
Set @NewTable = @Field1 + '-' + @Field2 + '-' + @Field3

USE [数据库B]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

SET @sql = ' 
CREATE TABLE [dbo].[' + @NewTable + '](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Field1] [nvarchar](50) NULL,
	[Field2] [nvarchar](50) NULL,
	[Field3] [nvarchar](50) NULL,
 CONSTRAINT [PK_' + @NewTable + '] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]'

/****** 执行创建新表SQL  ******/
EXEC(@sql)


fetch next from cur_test into @Field1,@Field2,@Field3
end
close cur_test
deallocate cur_test