Bulk Insert

• Gezo

  Grasshopper

  Points: 17

  More actions

  May 13, 2015 at 11:30 am

  #318101

  I am trying to import csv file to sql table from remote network using bulk insert.

  I declared variables - @userid and @password to enable sql server read the folder. Could someone help me how to pass the variables. see my query below:

  declare @query varchar(1000)

  declare @max1 int

  declare @count1 int

  Declare @filename varchar(100)

  declare @Filepath varchar(500)

  declare @pattern varchar(100)

  declare @TableName varchar(128)

  declare @userid VARCHAR(25)

  declare @password VARCHAR(20)

  set @count1 =0

  set @Filepath = '\\loadcsvfile\2016\\Unify_Files\'

  set @pattern = '*.csv'

  set @TableName = 'tbl_Employee'

  set @userid = 'Dxxxxx'

  set @password 'xxxxxx'

  create table #x (name varchar(200))

  set @query ='master.dbo.xp_cmdshell "dir '+@Filepath+@pattern +' /b"'

  insert #x exec (@query)

  delete from #x where name is NULL

  select identity(int,1,1) as ID, name into #y from #x

  drop table #x

  set @max1 = (select max(ID) from #y)

  --print @max1

  --print @count1

  While @count1 <= @max1

  begin

  --Set @Filepath = 'NET USE \\susagpwfm01\TRE_Files urban2009/User:worldshipups1 '

  Set @Pattern = '*.csv'

  set @count1=@count1+1

  set @filename = (select name from #y where [id] = @count1)

  set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = '''')'

  --Print @TableName

  --Print @FilePath

  --Print @FileName

  print @query

  exec (@query)

  Message #53871

• manub22

  Hall of Fame

  Points: 3163

  More actions

  May 13, 2015 at 1:10 pm

  #1797654

  What error you are getting?

  I think you have not specified the database and schema name, can you try this:

  set @query ='BULK INSERT dbname.dbo.'+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = '''')'

  ---

  ~manoj | LinkedIn | Facebook | YouTube
  Blog: SQLwithManoj.com