current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to insert data from multiple files into SQL Server. This is the code I am using--

DECLARE @MyCounter int;
DECLARE @Fileprefix nvarchar, @Filesuffix nvarchar, @fullname nvarchar, @Counter_string nvarchar;

SET @MyCounter = 1;
SET @Fileprefix= 'C:\Arvind_gpd\patents\';
SET @Filesuffix='data_corrected.csv';

WHILE (@MyCounter < 10)

BEGIN;
Set @Counter_string= Cast(@MyCounter AS varchar(1) );
Set @fullname = (@Fileprefix+ @Counter_string + @Filesuffix );
BULK INSERT GooglePatentsIndividualDec2012.dbo.patent
FROM  @fullname WITH  ( DATAFILETYPE = 'char', FIELDTERMINATOR = '^', ROWTERMINATOR =     '\n' );
SET @MyCounter = @MyCounter + 1;
END;  
GO

However I am getting these error messages--

  Incorrect syntax near @fullname. Expecting Integer, String, TEXT_LEX.....
  Incorrect syntax near DATAFILETYPE. Expecting SELECT or '('

What am I doing wrong in the above query?

share|improve this question
    
Why the ; after set? –  Kermit Jan 19 '13 at 15:32

2 Answers 2

up vote 0 down vote accepted

You cannot use a variable as a table name. If you want to use a variable you have to create a string and use the EXEC function like this:

DECLARE @fullname nvarchar, @sql nvarchar
SET @sql = 'SELECT * FROM ' + @fullname+ ' WHERE id = 1'
EXEC(@sql)
share|improve this answer
up vote 2 down vote

You cannot use a variable as a filename. According to the syntax described in MSDN it has to be a constant.

You may create the command and use EXEC to execute it.

set @Command = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent '
set @Command = @Command + 'FROM  '''+@fullname+''' WITH  ( '
set @Command = @Command + 'DATAFILETYPE = ''char'', '
set @Command = @Command ü 'FIELDTERMINATOR = ''^'', ROWTERMINATOR =     ''\n'' )'

EXEC (@Command)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.