question

The script in question looks like the script in the answer to the [related question][1] which was posted a couple of weeks ago. In this case, here is the same script restated as the stored procedure. All the changes which were needed to convert the script to the procedure are due to the fact that the openrowset does not accept the path to the file as a parameter, so the openrowset itself needs to be converted to dynamic script, that it all. The script to create procedure includes some comments which explain what has been done: if exists ( select 1 from sys.objects where [object_id] = object_id(N'dbo.usp_ImportRecordsFromFile') and [type] = N'P' ) drop proc dbo.usp_ImportRecordsFromFile; go set ansi_nulls on; set quoted_identifier on; go /* This procedure will read the records from the file provided as a procedure parameter, drop and create table named StagingTable, and insert the records from the file into it. The procedure will handle comma and pipe-delimited files and will also handle the files which have every value wrapped into double quotes (text qualified). It will accommodate the files from Windows (CRLF, a.k.a. \r\n or 0x0d0a used as line break) and Unix (LF, a.k.a. \n or 0x0a used as line break). It will not, however, accommodate the files from Mac (line breaks are carriage return \r). If handling of such files is needed then the case statement inside of the script setting the value of @headersSelect will have to be modified to accommodate handling of the files using just carriage return for line breaks. Sample usage (please note that the path to the file is from the server perspective): exec dbo.usp_ImportRecordsFromFile 'c:\temp\4358-unix.txt'; Notes: The file must exist, must be accessible by the SQL Server instance service account, and is expected to be comma or pipe-delimited with \n or \r\n used for line breaks. The errors are not handled, please add error handling as needed / required. */ create proc dbo.usp_ImportRecordsFromFile ( @fileName varchar(255) ) as begin set nocount on; -- local variables declare @headers varchar(max); declare @rowTerminator varchar(6); declare @delimiter varchar(3); declare @createTable nvarchar(max); declare @bulkInsert nvarchar(max); declare @lastColumn int; declare @updateScript nvarchar(max); -- The static select from openrowset will not work if the file name is provided via parameter -- because openrowset itself does not accept parameters, so the query needs to become dynamic. -- The values of the @headers and @rowTerminator are set by the dynamic script. declare @headersSelect nvarchar(max) = ' select @headersOut = replace(substring(t.BulkColumn, 1, charindex(char(10), t.BulkColumn) - 1), char(13), ''''), @rowTerminatorOut = case when charindex(char(13) + char(10), t.BulkColumn) > 0 then ''0x0d0a'' else ''0x0a'' end from openrowset(bulk ''' + @fileName + ''', single_clob) t;' -- read the first line of the file and set the values of @headers and @rowTerminator exec sp_executesql @statement = @headersSelect, @params = N'@headersOut varchar(max) out, @rowTerminatorOut varchar(6) out', @headersOut = @headers out, @rowTerminatorOut = @rowTerminator out; -- Header and row terminator values are already available; figure -- out delimiter and column names, and generate bulk insert script select @delimiter = case when charindex('","', @headers) > 0 then '","' when charindex('"|"', @headers) > 0 then '"|"' when charindex(',', @headers) > 0 then ',' else '|' end, -- outer replace is needed to remove 2 double quotes (in the beginning and the end of the headers) @createTable = replace('create table dbo.StagingTable(' + replace(@headers, @delimiter, ' varchar(50) null,') + ' varchar(50) null)', '"', ''), @bulkInsert = 'bulk insert dbo.StagingTable from ''' + @fileName + ''' with ( fieldterminator = ''' + @delimiter + ''', firstrow = 2, rowterminator = ''' + @rowterminator + ''' );' -- drop the staging table if already exists if exists( select 1 from sys.objects where [object_id] = object_id(N'dbo.StagingTable') and [type] = N'U' ) drop table dbo.StagingTable; -- create staging table exec sp_executesql @statement = @createTable; -- bulk insert records from delimited file exec sp_executesql @statement = @bulkInsert; -- Final touch up, which is needed only if the file included the double quotes as the -- text qualifier: if text is qualified then bulk insert worked but first column ended -- up prefixed and the lastcolumn - suffixed with the double quotes. Remove both. if charindex('"', @delimiter) > 0 begin select @lastColumn = max(column_id) from sys.columns where [object_id] = object_id(N'dbo.StagingTable'); select @updateScript = 'update dbo.StagingTable set ' + stuff(( select ', ' + quotename([name]) + ' = replace(' + quotename([name]) + ', ''"'', '''')' from sys.columns where [object_id] = object_id(N'dbo.StagingTable') and column_id in (1, @lastColumn) for xml path('')), 1, 2, ''); exec sp_executesql @statement = @updateScript; end; set nocount off; end; go Please change the name of the procedure to whatever it actually needs to be. Hope this helps. Oleg [1]: https://ask.sqlservercentral.com/questions/145000/load-txt-csv-files-with-minimal-effort-and-fast.html