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
Total Pageviews
Subscribe to:
Post Comments (Atom)
Blog Archive
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...
-
All we need to use the Custom paging in grid view without object data source. Here i am going to explain my code which is used in custom pa...
-
One of the common requests in ASP.NET is to submit a form when visitor hits an Enter key. That could be a case if, for example you want to m...
-
using System.Data; using System.Data.SqlClient; using System.Text; using System.IO; using iTextSharp.text; using iTextSharp.text.pdf; ...
No comments:
Post a Comment