Friday, September 18, 2009

Bulk Insert From CSV OR Text File In SQL SERVER 2005

One of my friends contacted me recently and said they needed some help creating a stored procedure that imported data from a text file. They wanted the procedure to accept three parameters: PathFileName, ColumnDelimter, and RowDeliter. The PathFileName is simply the name and physical location of the source file on the hard drive, the Delimer are used to identify Columns and rows respectively.
So that i will have give her following store procedure.


Create Proc BulkInsertFromFile
(
@FileName Varchar(500), -- depends on path
@ColumnDelimier Varchar(10), -- delimiter for columns
@RowDelimier Varchar(10)--, -- delimier for Rows  
--@Output varchar(50)  -- used to specify error or success msg
)
As

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist @FileName, @doesExist OUTPUT
SET NOCOUNT OFF

-- i m creating Temp Table to demonstrate the example
-- Temp table holds the Results copies from FIle

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

Declare @SQL  nVarchar(max)  
Declare @Param nVarchar(500)
Set @Param='@FileName Varchar(500),@ColumnDelimier Varchar(20),@RowDelimier Varchar(20)'
SET @SQL = 'BULK INSERT #Temp FROM '''+@FileName+'''
WITH
(
FIELDTERMINATOR ='''+@ColumnDelimier+''',
ROWTERMINATOR ='''+@RowDelimier+''',
FIRE_TRIGGERS
)'

print @SQL
EXEC sp_executesql @SQL,@param,@FileName,@ColumnDelimier,@RowDelimier
--    Set @Output='Bulk insert Completed'
END
--Else
--Begin
--    Set @Output='File does not exist'
--End

---- u can also use the output  param to specify the error msg


Select * From #Temp

Drop table #Temp



--------------------------------------------------------------------------------------------
Happy programming to all.....
Thnx a lots

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...