Hi Jeff,
You know, it's a whole new SQL world for me when I see you statements, and I take couple of hours to understand it even when you kindly explain every single programming line and I'm really glad for that.
I did create the SP ImportEqFiles running the statment you created and it was successfully created (Message: Command(s) completed successfully.), then I executed the EXEC dbo.ImportEqFiles and I got the message:
BULK INSERT #eq_Staging
FROM 'C:\Temp\eq_files\Eq_sample_1.csv'
WITH (
FIELDTERMINATOR = ','
, FIRSTROW = 2
, ROWTERMINATOR = ''
, CODEPAGE = 'ACP'
, DATAFILETYPE = 'widechar'
);
UPDATE dbo.FileLog
SET StatusDate = GETDATE()
, FileStatus = 'Staged'
WHERE FilePath = 'C:\Temp\eq_files\'
AND FileName = 'Eq_sample_1.csv'
BULK INSERT #eq_Staging
FROM 'C:\Temp\eq_files\Eq_sample_2.csv'
WITH (
FIELDTERMINATOR = ','
, FIRSTROW = 2
, ROWTERMINATOR = ''
, CODEPAGE = 'ACP'
, DATAFILETYPE = 'widechar'
);
UPDATE dbo.FileLog
SET StatusDate = GETDATE()
, FileStatus = 'Staged'
WHERE FilePath = 'C:\Temp\eq_files\'
AND FileName = 'Eq_sample_2.csv'
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
I can see 2 rows applying a SELECT * FROM dbo.FileLog but when I apply a SELECT * FROM #eq_Staging and SELECT * FROM #Directory I got the same error message:
Msg 208, Level 16, State 0, Line 1 Invalid object name '#eq_Staging'.
Seems that they are not within this DB, so, I might be doing something wrong.
The weird thing is that even when I run both of this CREATE TABLE statements for the #eq_Staging table separatelly in other window I get the
"Message: Command(s) completed successfully."
but I still can't find them in my DB Object Explorer (in my DB objects tree).
I ran also
CREATE TABLE #Directory
(
ObjectName VARCHAR(450)
, Depth TINYINT
, IsFile TINYINT
)
but I still can't find those tables in anywhere...
I am looking at this for the last few hours but I can't find the solution on how parse the cleaned information to final table because I could't find the #eq_Staging and the #Documentation table so, I will wait for the next hints to proceed with the "ToDo" section.
Thanks a lot!!!