July 13, 2013 at 10:16 am
while trying to hone my skils on T-SQL, i came across a procedure that
Load blank per-activity data files for multi-instance activities to a DB's BlankData table, then it should be
-- creating this table if BlankData isn't in the DB and recreating it otherwise.
THE PROCEDURE IS AS BELOW)
-- Directory containing files to load specified as a @path argument to this procedure.
-- Directory containing files to load specified as a @path argument to this procedure.
MY QUESTIONS:
1. should procedure warn if table already present before deleting BlankData?
2. HOW DO I :
-- -. add checks for the following conditions, with suitable messages
-- -. failed "exec xp_cmdshell @cmd" command
-- -. @path's referencing a directory that's devoid of .xml files
-- -. failed attempts to read .xml files
-- -. failed "select name from #filenames where name like '%.xml'" command
-- -. failed "exec (@sql)" command
3. -. should option be added for writing messages to a log?
4. HOW TO:
collapse all sp_Load<documentXX>toDB procedures to a single,
-- parameterized procedure
5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
-- -. specifies qualifier (e.g., 201308) for table from which to load documents
-- -. defaults to value given by a new "current epoch" function
-- concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
-- blank documents from different tables in a set of related tables:
-- e.g.., BlankData_201308, BlankData_201309...
THE PROCEDURE
CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB]
@path varchar(256)
AS
BEGIN
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
RECONFIGURE
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
RECONFIGURE
SET NOCOUNT ON;
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'BlankData'))
BEGIN
print('exist')
drop table fas.dbo.[BlankData]
END
create table BlankData(id int identity(1,1),fileName varchar(256),blankFile xml)
declare @cmd varchar(256)
set @cmd = 'dir /b ' +'"'+ @path+'"'
create table #filenames(name varchar(256))
insert into #filenames
exec xp_cmdshell @cmd
declare @file nvarchar(256)
declare fileNameCursor CURSOR SCROLL FOR
select name from #filenames where name like '%.xml'
open fileNameCursor
fetch next from fileNameCursor
into @file
WHILE @@FETCH_STATUS = 0
begin
declare @sql varchar(max)
--insert into fas.dbo.SampleData(fileName) values (@file)
set @sql =
'insert into [fas].[dbo].[BlankData]
select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'
exec (@sql)
FETCH NEXT FROM fileNameCursor
INTO @file
end
CLOSE fileNameCursor
DEALLOCATE fileNameCursor
DECLARE @fileCount int
select @fileCount = COUNT(*) from #filenames
print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
select @fileCount = COUNT(*) from BlankData
print (convert(varchar(max),@fileCount) +' xml files are imported')
select name as 'File Not Imported'
from #filenames
where name not in
(select fileName from fas.dbo.BlankData)
select fileName as 'File Imported'
from BlankData
END
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply