sql server存储过程,用于读取文件信息 [英] sql server stored procedure for reading file information
本文介绍了sql server存储过程,用于读取文件信息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我必须编写存储过程,将文件名和文件路径以及文件类型插入到table.Please帮助我。
解决方案
您可以使用如下所述的BULK插入。
创建 表 #FileData
LineNumber int identity ( 1 , 1 ),
LineData nvarchar ( 4000 )
);
INSERT INTO #FileData
SELECT file .BulkColumn
FROM OPENROWSET
(
批量 ' < span class =code-string> C:\ yourpath \Temp \anyfile.txt',
single_clob
) file < /跨度>;
SELECT * FROM #FileData;
注意: - 上述示例中的文件名是硬编码的,您需要在实际代码中进行更改。
这可以帮助
CREATE 表 MainTable(Col1 INT ,Col2 VARCHAR ( 30 ),Col3 VARCHAR ( 30 ),FName VARCHAR ( 30 ) NULL ,FPath VARCHAR ( 20 ) NULL )
GO - 主要标签le
CREATE PROCEDURE ReadFile
@ FileName VARCHAR ( 20 ),
@ FilePath VARCHAR ( 20 )
AS
BEGIN
- SET @FilePath ='F:\data.txt'
CREATE 表 #temp(Col1 INT ,Col2 VARCHAR ( 30 ),Col3 VARCHAR ( 30 )) - 文本文件中的数据
DECLARE @ String VARCHAR (MAX)
SET @ String = ' '
SET @ String = < span class =code-string>' BULK INSERT #temp FROM''' + @ FilePath + ' ''
WITH
(
FIELDTERMINATOR ='', '',
ROWTERMINATOR =''\ n''
)'
EXEC ( @ String )
INSERT MainTable(Col1,Col2,Col3) SELECT * FROM #temp
更新 MainTable SET FName = @ FileName
更新 MainTable SET FPath = @ FilePath
DROP 表 #temp
结束
GO
EXEC ReadFile @ FileName = ' 您的文件名', @ FilePath = ' 文件路径'
SELECT * FROM MainTable
GO 跨度>
Hi all,
I have to write stored procedure which will insert file name and file path and type of file into table.Please help me.
解决方案
You can use BULK insert as mentioned below.
CREATE TABLE #FileData LineNumber int identity (1, 1), LineData nvarchar(4000) ); INSERT INTO #FileData SELECT file.BulkColumn FROM OPENROWSET ( bulk 'C:\yourpath\Temp\anyfile.txt', single_clob ) file; SELECT * FROM #FileData;
Note :- File name is hardcoded in above example which you need to change in your actual code.
May this helps
CREATE TABLE MainTable(Col1 INT, Col2 VARCHAR(30),Col3 VARCHAR(30),FName VARCHAR(30) NULL,FPath VARCHAR(20) NULL) GO--Main table CREATE PROCEDURE ReadFile @FileName VARCHAR(20), @FilePath VARCHAR(20) AS BEGIN --SET @FilePath = 'F:\data.txt' CREATE TABLE #temp(Col1 INT, Col2 VARCHAR(30),Col3 VARCHAR(30))--Data in text file DECLARE @String VARCHAR(MAX) SET @String = '' SET @String = 'BULK INSERT #temp FROM ''' + @FilePath + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )' EXEC (@String) INSERT MainTable(Col1,Col2,Col3) SELECT * FROM #temp UPDATE MainTable SET FName = @FileName UPDATE MainTable SET FPath = @FilePath DROP TABLE #temp END GO EXEC ReadFile @FileName = 'Your File Name',@FilePath = 'File Path' SELECT * FROM MainTable GO
这篇关于sql server存储过程,用于读取文件信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文