Bulk Insert

[be1s]

Here's a small piece code that I'm trying to run. I need to build by text file each time. Once built, I'd like to do a bulk insert, however it keeps complaining about my syntax in the "from" statement. All parameters have been declared, I'm just not showing it here.


if @currentmonth = 1 -- January
begin
set @champFile = '\CC' + @lastyear + '1001.TXT'
bulk insert ccm_Prov_Enct_Stat_Rep
from 'f:\' + @champFile
with (DATAFILETYPE = 'CHAR')
end

 

[Catadmin]

First question, What is the exact error message?

Second question, Why didn't you put the 'f:\' into the set statement?

Third, your from statement is setting the whole thing to be "f:\\CC<@lastyear>1001.TXT". You've got too many slashes in the file path.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[be1s]

The exact error msg is "Incorrect syntax near '@champFile'."

Ok here's the corrected code:

if @currentmonth = 1 -- January
begin
set @champFile = 'f:\CC' + @lastyear + '1001.TXT'
bulk insert ccm_Prov_Enct_Stat_Rep
from @champFile
with (DATAFILETYPE = 'CHAR')
end

 

[dbomrrsm]

declare @sql varchar (200)
if @currentmonth = 1  -- January
 begin
 set @champFile = 'f:\CC' + @lastyear + '1001.TXT' 
 set @sql = 'bulk insert ccm_Prov_Enct_Stat_Rep
 from '+@champFile+
 ' with (DATAFILETYPE  = ''CHAR'')
 end '

exec @SQL

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Quick question, DBomrrsm. Should the END keyword be within the single quotes like that?



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[dbomrrsm]

not too sure - not that well up on bulk insert syntax - just trying to show a method really

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Ah. Okay. I was just checking for future references sake.

You're one of the people whose code I try and remember in case I ever need something similar down the line.




Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[dbomrrsm]

Thank you for saying that - very kind

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]