Create Table and Bulk Insert

Question

Post reply

Create Table and Bulk Insert

Viewing 2 posts - 46 through 46 (of 46 total)

You must be logged in to reply to this topic. Login to reply

urso47 SSC Eights! Points: 979 More actions · Answer 1

I made the following changes in order to get the information into tables:

^{CREATE PROCEDURE dbo.ImportEqFiles}

/**********************************************************************************

Purpose:

Given a valid file path and the "LIKE" filter for file names, import the files from the given file path and log the

processing of each file in the FileLog table (which is created by this proc if it doesn't exist). Files that have

already been imported will not be imported again.

Usage Examples:

EXEC dbo.ImportEqFiles @pFilePath, @pFilter

EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'

**********************************************************************************/

--===== Define the parameters for this proc

@pFilePath VARCHAR(450)

, @pFileFilter VARCHAR(450)

AS

--===================================================================================

-- Presets

--===================================================================================

--===== Setup the environment

SET NOCOUNT ON; --Prevents false error returns to the calling procedure or GUI

SET XACT_ABORT ON; --Short circuits any explicit transactions (if they exist) when an error occurs.

--===== Conditionally drop the all Temp Tables to make reruns in SSMS easier

IF OBJECT_ID('tempdb..#eq_Staging','U') IS NOT NULL DROP TABLE #eq_Staging;

IF OBJECT_ID('tempdb..#Directory' ,'U') IS NOT NULL DROP TABLE #Directory;

--===== Create the staging table using all VARCHAR datatypes because

-- of all the double quotes in the file(s) we're loading.

CREATE TABLE #eq_Staging

(

cGroupID VARCHAR(200)

, cGroupIDBillable VARCHAR(200)

, cGroupName VARCHAR(200)

, cGroupDescription VARCHAR(200)

, cRowID VARCHAR(200)

, cRowName VARCHAR(200)

, cRowDescription VARCHAR(200)

, cTransactDate VARCHAR(200)

, cTransactType VARCHAR(200)

, cChargeAccountID VARCHAR(200)

, cChargeAccountType VARCHAR(200)

, cUserWhoPrinted VARCHAR(200)

, cDocumentName NVARCHAR(200)

, cUnits VARCHAR(200)

, cDocumentID VARCHAR(200)

, cDeviceIP VARCHAR(200)

, cDevModel VARCHAR(200)

, cDevManuf VARCHAR(200)

, cDuration VARCHAR(200)

, cDestination VARCHAR(200)

, cFullName VARCHAR(200)

, cPrimaryPIN VARCHAR(200)

, cWorkstation VARCHAR(200)

, cAmount VARCHAR(200)

, JobProperties VARCHAR(200)

, cAltCost VARCHAR(200)

, cDepartment VARCHAR(200)

, cBillable VARCHAR(200)

, cAlternatePin VARCHAR(200)

)

;

--===== Create the table to hold the directory information we'll need.

CREATE TABLE #Directory

(

ObjectName VARCHAR(450)

, Depth TINYINT

, IsFile TINYINT

)

;

--===== If the file processing log table doesn't already exist, create it now.

IF OBJECT_ID('dbo.FileLog','U') IS NULL

CREATE TABLE dbo.FileLog

(

FileLogID INT IDENTITY(1,1)

, FilePath VARCHAR(450) NOT NULL

, FileName VARCHAR(450) NOT NULL

, StatusDate DATETIME NOT NULL DEFAULT GETDATE()

, FileStatus VARCHAR(20) NOT NULL

, ProcessedBy VARCHAR(128) NOT NULL DEFAULT ORIGINAL_LOGIN()

, CONSTRAINT PK_FileLog PRIMARY KEY CLUSTERED (FileLogID)

, CONSTRAINT AK_FileLog UNIQUE (FilePath,FileName)

)

;

--===== Declare local variables

DECLARE @FullFileName VARCHAR(500)

, @SQL VARCHAR(MAX)

;

--===== Ensure the file path ends with a backslash and build the quoted file name

SELECT @pFilePath = CASE WHEN RIGHT(@pFilePath,1) = '\' THEN @pFilePath ELSE @pFilePath + '\' END

;

--===================================================================================

-- Load all of the filenames from the given path into the log

--===================================================================================

--===== Load all the file names using xp_dirtree which does NOT require xp_CmdShell to be enabled.

INSERT INTO #Directory

(ObjectName, Depth, IsFile)

EXEC xp_dirtree @pFilePath,1,1

;

--===== Add the files that aren't already in the log as "Import In Process" files that need to be processed.

INSERT INTO dbo.FileLog

(FilePath, FileName, StatusDate, FileStatus)

SELECT FilePath = @pFilePath, ObjectName, StatusDate = GETDATE(), FileStatus = 'Import inprocess'

FROM #Directory

WHERE IsFile = 1

AND ObjectName LIKE @pFileFilter

EXCEPT

SELECT FilePath, FileName, StatusDate = GETDATE(), FileStatus = 'Import inprocess'

FROM dbo.FileLog

;

--===================================================================================

-- Import the "Import inprocess" files to the staging table

--===================================================================================

SELECT @SQL = ISNULL(@SQL,'') + '

BULK INSERT #eq_Staging

FROM ' + QUOTENAME(@pFilePath+FileName,'''') + '

WITH (

FIELDTERMINATOR = '',''

, FIRSTROW = 2

, ROWTERMINATOR = ''''

, CODEPAGE = ''ACP''

, DATAFILETYPE = ''widechar''

);

UPDATE dbo.FileLog

SET StatusDate = GETDATE()

, FileStatus = ''Staged''

WHERE FilePath = ' + QUOTENAME(@pFilePath,'''') + '

AND FileName = ' + QUOTENAME(FileName ,'''') + '

'

FROM dbo.FileLog

WHERE FilePath = @pFilePath

AND FileName LIKE @pFileFilter

AND FileStatus = 'Import inprocess'

;

--== create and load information from #eq_staging table
SELECT * INTO EQUIT FROM #eq_Staging
;
--== add ID field on the equit table
ALTER TABLE EQUIT
ADD equit_id int IDENTITY(1,1) NOT NULL
;

--===== Display the commands that we're goig to run.

-- (Note that will be truncated for display at 8K but will be the full command in reality)

PRINT @SQL

--===== Import and log all the files identified by this run

EXEC (@SQL)

Result:

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 don't see anything wrong in this statement, does anyone?

Thanks.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 2

I guess I do't understand why you're adding and IDENTITY column to a permanent table on the fly after the table had been populated. Can't help you with a thing there because I wouldn't do such a thing.

As to the error, that's likely coming from the bulk insert. You need to find out if that's actually true because I can't tell from here. For example, when you run the proc, does #eq_staging actually exist?

I'm not sure what else you changed but you said you had this working. I can't troubleshoot this from afar. Peel one potato at a time and go through it one step at a time until each step works consistently and as expected.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)