{"id":5882,"date":"2017-01-18T21:13:22","date_gmt":"2017-01-18T21:13:22","guid":{"rendered":"http:\/\/www.jitendrazaa.com\/blog\/?p=5882"},"modified":"2017-03-09T17:11:12","modified_gmt":"2017-03-09T17:11:12","slug":"sql-server-read-all-files-in-directory-and-store-in-table","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","title":{"rendered":"SQL Server &#8211; Read all files in directory and store in Table"},"content":{"rendered":"<p style=\"text-align: justify;\">Recently I was in need to\u00a0analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of <strong>SQL Server<\/strong>.<\/p>\n<p style=\"text-align: justify;\">Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.<\/p>\n<p style=\"text-align: justify;\">Found very good article <a href=\"https:\/\/www.simple-talk.com\/sql\/t-sql-programming\/the-tsql-of-text-files\/\">here<\/a>\u00a0which helped me to come up with below queries. If you need to read more in detail about below T-SQL and stored procedures please navigate to original article.<!--more--><\/p>\n<p><strong>Table where we need to load files<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE &#x5B;dbo].&#x5B;DebugLogs](\r\n\t&#x5B;Id] &#x5B;numeric](18, 0) IDENTITY(1,1) NOT NULL,\r\n\t&#x5B;LogText] &#x5B;nvarchar](max) NULL,\r\n\t&#x5B;FileName] &#x5B;nchar](500) NULL\r\n) ON &#x5B;PRIMARY] TEXTIMAGE_ON &#x5B;PRIMARY]\r\n<\/pre>\n<p><strong>Stored procedure which reads all files in a directory<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE &#x5B;dbo].&#x5B;ListPathsXML]\r\n@FileSpec VARCHAR(2000),\r\n@order VARCHAR (80) = '\/O-D',--sort by date time oldest first\r\n@xmlFileList XML OUTPUT\r\n \r\nAS\r\nDECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, FullPath VARCHAR(2000))\r\nDECLARE @CommandLine VARCHAR(4000)\r\nIF @order IS NOT NULL -- abort if the order is silly\r\n   BEGIN\r\n   SELECT @CommandLine =LEFT('dir &quot;' + @FileSpec + '&quot; \/A-D \/B \/S '+@order,4000)\r\n   INSERT INTO @MyFiles (FullPath)\r\n       EXECUTE xp_cmdshell @CommandLine\r\n   DELETE FROM @MyFiles WHERE fullpath IS NULL\r\n       OR fullpath = 'File Not Found'\r\n   END\r\nSET @xmlFileList = (SELECT fullpath FROM @MyFiles\r\n                         FOR\r\n                          XML PATH('thefile'),\r\n                              ROOT('thefiles'),\r\n                              TYPE)\r\n\r\n<\/pre>\n<p><strong>Stored procedure which reads content of file one at a time<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE &#x5B;dbo].&#x5B;spLoadTextFromAFile]\r\n   @Filename VARCHAR(255),\r\n   @Unicode INT = 0\r\nAS \r\n   SET NOCOUNT ON\r\n   DECLARE @MySpecialTempTable VARCHAR(255)\r\n   DECLARE @Command NVARCHAR(4000)\r\n   DECLARE @RESULT INT\r\n\r\n--firstly we create a global temp table with a unique name\r\n   SELECT   @MySpecialTempTable = '##temp' \r\n       + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))\r\n--then we create it using dynamic SQL, \r\n   SELECT   @Command = 'create table &#x5B;' \r\n       + @MySpecialTempTable + '] (MyLine '\r\n       + CASE WHEN @unicode &lt;&gt;0 THEN 'N' ELSE '' END +'varchar(MAX))\r\n '\r\n   EXECUTE sp_ExecuteSQL @command\r\n\r\n   SELECT   @command = 'bulk insert &#x5B;' \r\n       + @MySpecialTempTable + '] from' + ' ''' \r\n       + REPLACE(@Filename, '&quot;', '') + '''' \r\n       + ' with (FIELDTERMINATOR=''|~||''' + ',ROWTERMINATOR = ''\r\n'''        + CASE WHEN @unicode &lt;&gt;0 THEN ', DATAFILETYPE=''widechar'''ELSE '' END\r\n       + ')'\r\n-- import the data\r\n   EXEC (@Command)\r\n   EXECUTE ('Select * from ' + @MySpecialTempTable)\r\n   EXECUTE ('Drop table ' + @MySpecialTempTable)\r\n<\/pre>\n<p style=\"text-align: justify;\"><strong>Final T-SQL query to read content of every file exists in directory and store in Table in SQL Server<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDECLARE\r\n       @ii INT,\r\n       @iiMax INT,\r\n       @File VARCHAR(2000)\r\nDECLARE @files TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, &#x5B;Path] VARCHAR(2000))\r\nDECLARE @lines TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY, &#x5B;line] NVARCHAR(MAX), fName nVARCHAR(200))\r\n \r\nDECLARE @FileList XML\r\nEXECUTE ListPathsXML 'C:\\ForceUtil\\18_Jan_2017\\DebugLogs\\',\r\n    DEFAULT , @XMLFileList = @FileList OUTPUT\r\n \r\nINSERT INTO @files(path)\r\n   SELECT   x.thefile.value('fullpath&#x5B;1]', 'varchar(2000)') AS &#x5B;path]\r\n        FROM    @FileList.nodes('\/\/thefiles\/thefile') AS x ( thefile )\r\nDELETE FROM @files WHERE REVERSE(path) LIKE 'golrorre%'\r\n--don't look at the current errorlog!\r\nSELECT @ii=1, @iiMax=MAX(MyID) FROM @Files\r\nWHILE @ii&lt;=@iiMax\r\n   BEGIN\r\n   SELECT @File= &#x5B;path] FROM @files WHERE MyID=@ii\r\n   INSERT INTO @lines(line)\r\n       EXECUTE spLoadTextFromAFile @file, @Unicode=1\r\n\r\n   INSERT INTO &#x5B;dbo].&#x5B;DebugLogs] (LogText, &#x5B;FileName])\r\n\tSELECT  Line, @file FROM @lines\r\n   \r\n   --Empty temp table\r\n   Delete FROM @lines\r\n\r\n   SELECT @ii=@ii+1\r\n   END\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>T-SQL Script to read all files in directory and store back in table &#8211; SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292],"class_list":["post-5882","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":5873,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","url_meta":{"origin":5882,"position":0},"title":"Export Documents saved as Blob \/ Binary from SQL Server","author":"Jitendra","date":"December 24, 2016","format":false,"excerpt":"T-SQL Scripts to Export Blob or Binary data stored in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Export Blob as File","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":5828,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-search-complete-database-for-value\/","url_meta":{"origin":5882,"position":1},"title":"SQL Server \u2013 Search complete database for value","author":"Jitendra","date":"November 26, 2016","format":false,"excerpt":"Search complete Database in SQL Server for some value","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server search database for value","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2861,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/pagination-and-switch-case-in-where-clause-sql-server\/","url_meta":{"origin":5882,"position":2},"title":"Pagination and Switch Case in Where clause &#8211; SQL Server","author":"Jitendra","date":"May 19, 2012","format":false,"excerpt":"Example of using pagination and switch case in where clause in SQL server","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Employee Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Employee-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":454,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/drop-stored-procedure-if-exist-in-sql-server\/","url_meta":{"origin":5882,"position":3},"title":"Drop Stored Procedure if exist in SQL Server","author":"Jitendra","date":"June 30, 2010","format":false,"excerpt":"How to drop the stored procedure in SQL Server if exist","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1125,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-table-information-column-name-data-type-in-sql-server\/","url_meta":{"origin":5882,"position":4},"title":"Get Table information (Column Name, Data Type) in SQL Server","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server INFORMATION_SCHEMA.COLUMNS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-INFORMATION_SCHEMA.COLUMNS-300x44.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":448,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/search-for-table-in-sql-server\/","url_meta":{"origin":5882,"position":5},"title":"Search for Table in SQL Server","author":"Jitendra","date":"June 30, 2010","format":false,"excerpt":"In\u00a0organization, where huge number of tables present in database. its very hard to search the particular table. Here few SQL queries to search the table name by pattern. Search For Table whose name starts with A [sourcecode lang=\"sql\"] SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 AND\u2026","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"List of Non clustered Tables in SQL Server 2005","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/06\/Non-clustered-Tables.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5882","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=5882"}],"version-history":[{"count":3,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5882\/revisions"}],"predecessor-version":[{"id":5914,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5882\/revisions\/5914"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=5882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=5882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=5882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}