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=0
declare @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 ' + @TableName
declare @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=@TableName
	for xml path('')
),1,1,'')
,@SetStr=Stuff((
	select '+ ''*,'' + Convert(nvarchar(max),isNull(' + column_name + ','''')) '
	from INFORMATION_SCHEMA.COLUMNS 
	where table_name=@TableName
	for xml path('')
),1,1,'')

-- 动态组出SQL
SET @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)
begin
	set @i = @i + 1

	-- 执行动态组出的SQL
	EXECUTE sp_executesql @SQLCommand, N'@Sort int,@Str nvarchar(max)=''''', @i
end

最後印出来是这样