本文主要是介绍【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 NULLDROP 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 NULLDrop Table #Dir
Create Table #Dir
(id_num INT IDENTITY(1,1),FName NVARCHAR(3000) null
)IF OBJECT_ID('TempDB..#Dir_a') IS NOT NULLDrop 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
BeginDrop Table #TempT
EndCreate 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, 1INSERT INTO #temp_Path
(path_A,depth)
SELECT distinct @FilePath + subdirectory, depth
from #temp_tree
where isfile = 0
and depth = 1Declare @max_D intselect @max_D = max(Depth)
from #temp_tree
where isfile = 0select '一共有' + 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 = 1Declare @max_Depth intselect @max_Depth = max(Depth)
from #temp_tree
where isfile = 0while @Depth_use < @max_Depth
BeginINSERT INTO #temp_Path(path_A,depth)select distinct t1.path_A + '\' + t2.subdirectory, t2.depthfrom #temp_Path t1(nolock),#temp_tree t2(nolock)where t1.depth = @Depth_useand t2.depth = t1.depth + 1and t2.isfile = 0Declare @path_checkuse varchar(1000)Declare @cmd_pathcheck varchar(1000)Declare @path_checkresult intDeclare @x int = 1Declare @y int select @y = max(Item) from #temp_Pathwhile @x <= @yBeginTruncate table #DirSELECT @path_checkuse = ''select @path_checkuse = path_Afrom #temp_Pathwhere item = @xIF ISNULL(@path_checkuse,'') <> ''Begin select @cmd_pathcheck = 'dir /O /D /B ' + @path_checkuseinsert into #Direxec Master.dbo.XP_CMDSHELL @cmd_pathcheckIF EXISTS (SELECT 1 FROM #Dir WHERE IsNULL(FNAME,'找不到檔案') not like '%找不到檔案%')BeginINSERT INTO #Dir_a(FName)SELECT @path_checkuse + '\' + FNAME FROM #Dir WHERE IsNULL(FNAME,'找不到檔案') not like '%找不到檔案%'update #temp_Pathset path_check = 1where item = @xEndEndTruncate table #Dirselect @x = @x + 1Enddelete from #temp_Pathwhere ISNULL(path_check,'') = ''select * from #temp_Pathselect @Depth_use = @Depth_use + 1
EndRETURN
--撈路徑下所有的文件 Endselect * from #temp_Path
order by Itemdelete 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_awhile @a <= @b
Beginselect @Red_FileName = FNamefrom #Dir_awhere id_num = @aTruncate TABLE #TempTExec (' BULK INSERT #TempT FROM ''' + @Red_FileName + '''' +' WITH (FIRSTROW = 1,DATAFILETYPE = ''char'',FIELDTERMINATOR = '''',KEEPNULLS) ')insert into [SQL_LeadingIN](SPName,Content)select @Red_FileName, Colum1from #TempTwhere ISNULL(Colum1,'') <> ''Truncate TABLE #TempTselect @a = @a + 1
Endselect * from [SQL_LeadingIN](nolock)这篇关于【SQL】將SQL文件匯入數據庫的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
