這其實是遇到無法用大量匯入~
卻又想要將指定資料表匯入到另一個資料庫的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

最後印出來是這樣