bulk insert using a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

bulk insert using a stored procedure

Author

Topic

signforarun
Starting Member

1 Post

Posted - 2012-01-04 : 06:49:03

Have writen a procedure to bulk insert using a stored procedure

BULK
INSERT test
FROM 'D:\bcp\check.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Proc IS

CREATE PROCEDURE BULKINSERTTXT
(
@TABLENAME VARCHAR(50),
@PATHNAME VARCHAR(50)
)
AS
BEGIN
DECLARE @SSQL NVARCHAR(50);
SET @SSQL= SELECT ' BULK INSERT ' + '@TABLENAME' + ' FROM '' ' + '@PATHNAME' + ' '' WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
) GO'
exec sp_ExeCuteSQL @SSQL
END

Problem with this is the query becomes like the below
BULK INSERT @TABLENAME FROM ' @PATHNAME ' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO

Now @PATHNAME is not a parameter

Also tried without sinqle quote in dynamic query as
BULK INSERT @TABLENAME FROM @PATHNAME WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO and exec the procedure as
Exec BULKINSERTTXT ‘tablename’, ’'D:\bcp\check.txt'’ this also so error
Kindly provide solution for this

Arun Kumar.B
Mobius Knowledge Services

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-04 : 06:53:53

[code]
SET @SSQL= ' BULK INSERT ' + @TABLENAME + ' FROM ''' + @PATHNAME + ''' WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
) GO'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources