SQL模拟资料汇出及印出新增资料插入~

这其实是遇到无法用大量汇入~
却又想要将指定资料表汇入到另一个资料库的SQL方式@@
因为在独立环境只能先存在记事本~再贴过去新增@@~
写了这东东...XD

资料表

CREATE TABLE [Test]([Test_ID] [int] NOT NULL,[Test_Name] [nvarchar](50) NULL,[Test_Sort] [int] NULL default 0,[Test_Create_Date] [datetime] NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([Test_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]

模拟资料汇入

declare @i int=0,@Count int=0declare @TargetID int,@TargetName nvarchar(50)=''declare @SQLCommand nvarchar(max)declare @TableName nvarchar(50) = N'Test',@MainKey nvarchar(50) = 'Test_ID',@StrName nvarchar(max),@SetStr nvarchar(max)-- 纪录变数资料表有几笔declare @CountTable nvarchar(max) = 'select @CountOut=Count(0) from ' + @TableNamedeclare @CountSetOut nvarchar(max) = '@CountOut int OUTPUT'exec sp_executesql @CountTable,@CountSetOut,@CountOut=@Count OUTPUT--取得变数资料表的所有栏位名称select @StrName=Stuff((select ',' + column_name from INFORMATION_SCHEMA.COLUMNS where table_name=@TableNamefor xml path('')),1,1,''),@SetStr=Stuff((select '+ ''*,'' + Convert(nvarchar(max),isNull(' + column_name + ','''')) 'from INFORMATION_SCHEMA.COLUMNS where table_name=@TableNamefor xml path('')),1,1,'')-- 动态组出SQLSET @SQLCommand = 'select @Str=' + @SetStr + ' from (select Row_Number()Over(order by ' + @MainKey + ') CountSort,' + @StrName + ' from ' + @TableName + ' ) k where CountSort = @Sort 'SET @SQLCommand += ' print ''insert into ' + @TableName + '(' + @StrName + ') select N'''''' + replace(substring(@Str,3,len(@Str)), ''*,'' , '''''',N'''''' ) + '''''''' '--列印所有笔数的变数资料表的资料while(@i<@Count)beginset @i = @i + 1-- 执行动态组出的SQLEXECUTE sp_executesql @SQLCommand, N'@Sort int,@Str nvarchar(max)=''''', @iend

最后印出来是这样