Como realizar carga masiva de archivos con Sql Server 2008 R2 (Bulk Insert)

A continuación describiremos como realizar una carga masiva directamente desde sql server a través de un procedimiento almacenado (SP). Para este procedimiento utilizaremos una función de sql server llamada Bulk Insert cuya sintaxis es la siguiente:

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )]

Pero para efectos prácticos solo utilizaremos la siguiente estructura:

BULK INSERT 
   [ database_name . [ schema_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
      [ BATCHSIZE = batch_size ] 
      [ [ , ] FORMATFILE = 'format_file_path' ] 
    )]

Para este proceso debemos tener un archivo de formato, nuestro archivo de carga y obviamente nuestra tabla de destino. Para este ejemplo utilizaremos los siguiente:

Tabla cliente

CREATE TABLE [database_name].[cliente](
	[nombre] [varchar](30) NULL,
	[rut] [varchar](10) NULL,
	[telefono] [varchar](10) NULL
) ON [PRIMARY]

GO

Archivo Formato (cliente.ftm)

10.0
3
1    SQLCHAR  0 30   ""     1   nombre    SQL_Latin1_General_CP1_CI_AS
2    SQLCHAR  0 10   ""     2   rut       SQL_Latin1_General_CP1_CI_AS
3    SQLCHAR  0 10   "\r\n" 3   telefono  SQL_Latin1_General_CP1_CI_AS

Los nombres y largos de los campos deben coincidir con los de la base de datos.
Si lo dejamos así separara el archivo por cantidad de caracteres si lo quieren por delimitadores se deben agregar en » » (ejemplo «;»).
La ultima fila debe llevar siempre «\r\n»
10.0 Corresponde a la versión utilizada por sql server
3 Corresponde a la cantidad de filas

Archivo de Carga (cliente_masivo.txt)

Pedro Juan Lopez Ortega       11111111-80991234567    
Pedro Juan Lopez Ortega       11111111-80991234567
Pedro Juan Lopez Ortega       11111111-80991234567
Pedro Juan Lopez Ortega       11111111-80991234567
Pedro Juan Lopez Ortega       11111111-80991234567
.....

Bueno con estos datos estamos listos para realizar nuestra carga de datos masivos. Para no alargar mas este tutorial les dejo el procedimiento almacenado y la forma de uso:

USE [My DataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SpCargaMasiva] 
	@Archivo varchar(255), 
	@Formato varchar(255),
	@Tabla varchar(150)
AS
BEGIN TRY
declare @Sql nvarchar(1000);
declare @FilasDevueltas bigint;

SET NOCOUNT OFF;

set @Sql='TRUNCATE TABLE ' + @Tabla
EXECUTE sp_executesql @Sql;

set @Sql = 'BULK INSERT ' + @Tabla +
		' FROM ' + CHAR(39) + @Archivo + CHAR(39) +
		' WITH (FORMATFILE = '  + CHAR(39) + @Formato + CHAR(39) +  
		',BATCHSIZE =1000)'
     
EXECUTE sp_executesql @Sql;

set @FilasDevueltas=@@ROWCOUNT;
select @FilasDevueltas as FilasDevueltas;

END TRY
BEGIN CATCH
	set @FilasDevueltas=-1;
	select @FilasDevueltas as FilasDevueltas;

END CATCH

Al comienzo limpia la tabla (en caso que no necesiten limpiar solo borran esa parte).
Controla algunos errores que puedan ocurrir retornando -1.

Modo de uso:

dbo.SpCargaMasiva 'C:\cliente.txt','C:\cliente.fmt','cliente'

Si están trabajando apuntando a algún servidor por ejemplo QUALITY deben cambiar la ruta C:\ por la correspondiente \\QUALITY\…

Espero sea de su utilidad.

No duden en dejar sus inquietudes o comentarios.

Saludos.