【SQL】將SQL文件匯入數據庫

需求：本地文档，读取内容并写入SQLServer 

思路：

1.遍历本地路径

2.读取路径下所有的文档

3.批量汇入SQLServer

参考代码：

--建立導入表
Create table [dbo].[SQL_LeadingIN]
(
	SPName varchar(1000),
	Content varchar(max)
)

--用於遍歷的路徑
Declare @FilePath varchar(500)
select @FilePath = 'D$\SQL\dbo'  

IF OBJECT_ID('TempDB..#temp_tree')IS NOT NULL 
	drop table #temp_tree
create table #temp_tree
(
	subdirectory nvarchar(2000),
	depth int,
	isfile bit default 0,
)

IF OBJECT_ID('TEMPDB..#temp_Path') IS NOT NULL
	DROP TABLE #temp_Path
create table #temp_Path
(
	item int identity (1,1),	
	path_A varchar(max),
	depth int,
	path_check int
)

IF  OBJECT_ID('TempDB..#Dir') IS NOT NULL
	Drop Table #Dir
Create Table #Dir
(
	id_num  INT IDENTITY(1,1),
	FName  NVARCHAR(3000) null
)

IF  OBJECT_ID('TempDB..#Dir_a') IS NOT NULL
	Drop Table #Dir_a
Create Table #Dir_a
(
	id_num  INT IDENTITY(1,1),
	FName  NVARCHAR(3000) null
)

IF  OBJECT_ID('TempDB..#TempT') IS NOT NULL
Begin
	Drop Table #TempT
End

Create Table #TempT
(
	Colum1 VARCHAR(8000) NULL
)

IF ISNULL(@FilePath,'') = '' 
	RETURN
IF RIGHT(@FilePath,1) <> '\'
	select @FilePath = @FilePath + '\'

insert into #temp_tree
exec master..xp_dirtree @FilePath, 0, 1

INSERT INTO #temp_Path
(path_A,depth)
SELECT distinct @FilePath + subdirectory, depth
from #temp_tree
where isfile = 0
and depth = 1

Declare @max_D int

select @max_D = max(Depth)
from #temp_tree
where isfile = 0

select '一共有' + Convert(varchar(10),@max_D) + '層文件夾,參考時間 3 層撈了 1分40~2分鐘,可以把路徑範圍縮小,執行下一步'

select * 
from #temp_tree
where isfile = 0
order by depth
return

--撈路徑下所有的文件 Begin
Declare @Depth_use int 
select @Depth_use = 1

Declare @max_Depth int

select @max_Depth = max(Depth)
from #temp_tree
where isfile = 0

while @Depth_use < @max_Depth
Begin
	INSERT INTO #temp_Path
	(path_A,depth)
	select distinct t1.path_A + '\' + t2.subdirectory, t2.depth
	from #temp_Path t1(nolock),
	#temp_tree t2(nolock)
	where t1.depth = @Depth_use
	and t2.depth = t1.depth + 1
	and t2.isfile = 0

	Declare @path_checkuse varchar(1000)
	Declare @cmd_pathcheck varchar(1000)
	Declare @path_checkresult int
	Declare @x int = 1
	Declare @y int 
	select @y = max(Item) from #temp_Path

	while @x <= @y
	Begin
		Truncate table #Dir

		SELECT @path_checkuse = ''

		select @path_checkuse = path_A
		from #temp_Path
		where item = @x

		IF ISNULL(@path_checkuse,'') <> ''
		Begin	
			select @cmd_pathcheck = 'dir /O /D /B  ' + @path_checkuse

			insert into #Dir
			exec Master.dbo.XP_CMDSHELL @cmd_pathcheck

			IF EXISTS (SELECT 1 FROM #Dir WHERE IsNULL(FNAME,'找不到檔案') not like '%找不到檔案%')
			Begin
				INSERT INTO #Dir_a(FName)
				SELECT @path_checkuse + '\' + FNAME 
				FROM #Dir 
				WHERE IsNULL(FNAME,'找不到檔案') not like '%找不到檔案%'

				update #temp_Path
				set path_check = 1
				where item = @x
			End
		End

		Truncate table #Dir

		select @x = @x + 1
	End

	delete from #temp_Path
	where ISNULL(path_check,'') = ''

	select * from #temp_Path

	select @Depth_use = @Depth_use + 1
End

RETURN
--撈路徑下所有的文件 End

select * from #temp_Path
order by Item

delete from #Dir_a
where ISNULL(FName,'') not like  '%.sql%'

select distinct * from #Dir_a
ORDER BY id_num 

Declare @Red_FileName varchar(2000)
Declare @a int = 1
Declare @b int
select @b = max(id_num) from #Dir_a

while @a <= @b
Begin
	select @Red_FileName = FName
	from #Dir_a
	where id_num = @a

	Truncate TABLE #TempT

	Exec (' BULK INSERT #TempT FROM ''' + @Red_FileName + '''' +
	'  WITH (
	FIRSTROW   = 1,
	DATAFILETYPE = ''char'',
	FIELDTERMINATOR = '''',
	KEEPNULLS
	) ')

	insert into [SQL_LeadingIN]
	(SPName,Content)
	select @Red_FileName, Colum1
	from #TempT
	where ISNULL(Colum1,'') <> ''

	Truncate TABLE #TempT

	select @a = @a + 1
End

select * from [SQL_LeadingIN](nolock)