Bulk INsert

• Maverick100877

  SSCertifiable

  Points: 5240

  More actions

  June 20, 2006 at 4:41 am

  #74997

  Hi

  I have some 900 text files which I have to import in sql server.Now how do I import all the 900 files.

   

  If I use bulk insert or BCP then how can I insert all the 900 text files in one go.

   

  Any help would be really great.

   

• Bill Scott

  SSCrazy

  Points: 2338

  More actions

  June 21, 2006 at 8:40 am

  #645043

  set up a batch file with a command similar to the following:

  cd directory where files live

  for %f in (*) do bcp <Yourdatabase.owner.yourdbtable> in %f -S <YourServerName> -U <UserName> -P <Password>

   

• David McFarland

  SSChampion

  Points: 11815

  More actions

  June 21, 2006 at 8:50 am

  #645049

  Bill's solution should work quite well if all of the 900 files have the same layout, so they could use the same format file. If not, I'd still try to take advantage of the looping routine, so naming your format files the same as your data files, but with a different extension, should allow you to use the same concept.

   

• Bill Scott

  SSCrazy

  Points: 2338

  More actions

  June 21, 2006 at 9:03 am

  #645053

  Good point. If the files differ by some part of their name change the * in the parenthesies to a regular file pattern match.  for instance if you have files that start with Format1 the batch file would look more like

  cd directory where files live

  for %f in (Format1*) do bcp <Yourdatabase.owner.yourdbtable> in %f -S <YourServerName> -U <UserName> -P <Password>

  likewise you can play with the extensions

  cd directory where files live

  for %f in (*.Type1) do bcp <Yourdatabase.owner.yourdbtable> in %f -S <YourServerName> -U <UserName> -P <Password>

  The expression in the parenthesies is the same one you would use to do a DIR in the command line to get the list of the files you want

• Maverick100877

  SSCertifiable

  Points: 5240

  More actions

  June 22, 2006 at 4:10 am

  #645212

  I have got this procedure and it is working fine.

   

  Create procedure

  usp_ImportMultipleFiles @filepath varchar(500),

  @pattern

  varchar(100), @TableName varchar(128)

  as

  set quoted_identifier off

  declare

  @query varchar(1000)

  declare

  @max1 int

  declare

  @count1 int

  Declare

  @filename varchar(100)

  set

  @count1 =0

  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

  @count1=@count1+1

  set

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

  set

  @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"

  WITH ( FirstRow=6,FIELDTERMINATOR =''\t'',ROWTERMINATOR = ''\n'')'

  --print @query

  exec

  (@query)

  end

  drop table

  #y