Problemas com o Bulk Insert,
Pergunta
-
Salve a todos, estou com um baita problema e não encontro a solução em lugar algum.
Eu tenho a tabela abaixo:
CREATE TABLE [PSA].[ImportacaoCadastral]( [CodEmpresa] [varchar](4) NULL, [Matricula] [varchar](9) NULL, [Reservado1] [varchar](1) NULL, [Nome] [varchar](60) NULL, [CodigoDR] [varchar](2) NULL, [DescricaoDR] [varchar](24) NULL, [Reservado2] [varchar](6) NULL, [NivelSalarial] [varchar](5) NULL, [DataNascimento] [varchar](8) NULL, [DataAdmissao] [varchar](8) NULL, [DataDemissao] [varchar](8) NULL, [EstadoCivil] [varchar](1) NULL, [Sexo] [varchar](1) NULL, [Naturalidade] [varchar](25) NULL, [UfNaturalidade] [varchar](2) NULL, [Cpf] [varchar](11) NULL, [CodigoBanco] [varchar](3) NULL, [CodigoAgencia] [varchar](5) NULL, [CodigoConta] [varchar](11) NULL, [Endereco] [varchar](70) NULL, [Complemento] [varchar](40) NULL, [Numero] [varchar](10) NULL, [Bairro] [varchar](40) NULL, [Cidade] [varchar](40) NULL, [UF] [varchar](2) NULL, [CEP] [varchar](8) NULL, [Telefone] [varchar](20) NULL, [DataFalecimento] [varchar](8) NULL, [CodigoEct] [varchar](33) NULL, [Email] [varchar](60) NULL, [CodigoINSS] [varchar](15) NULL, [DataInicioInss] [varchar](8) NULL, [ValorBeneficioInss] [varchar](13) NULL, [Linha] [int] NULL ) ON [PRIMARY] GO
E criei o format file abaixo:
<?xml version="1.0" ?> - <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> - <RECORD> <FIELD ID="1" xsi:type="CharFixed" MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="2" xsi:type="CharFixed" MAX_LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="3" xsi:type="CharFixed" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="4" xsi:type="CharFixed" MAX_LENGTH="60" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="5" xsi:type="CharFixed" MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="6" xsi:type="CharFixed" MAX_LENGTH="24" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="7" xsi:type="CharFixed" MAX_LENGTH="6" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="8" xsi:type="CharFixed" MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="9" xsi:type="CharFixed" LENGTH="8" /> <FIELD ID="10" xsi:type="CharFixed" LENGTH="8" /> <FIELD ID="11" xsi:type="CharFixed" LENGTH="8" /> <FIELD ID="12" xsi:type="CharFixed" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="13" xsi:type="CharFixed" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="14" xsi:type="CharFixed" MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="15" xsi:type="CharFixed" MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="16" xsi:type="CharFixed" MAX_LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="17" xsi:type="CharFixed" MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="18" xsi:type="CharFixed" MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="19" xsi:type="CharFixed" MAX_LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="20" xsi:type="CharFixed" MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="21" xsi:type="CharFixed" MAX_LENGTH="40" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="22" xsi:type="CharFixed" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="23" xsi:type="CharFixed" MAX_LENGTH="40" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="24" xsi:type="CharFixed" MAX_LENGTH="40" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="25" xsi:type="CharFixed" MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="26" xsi:type="CharFixed" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="27" xsi:type="CharFixed" MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="28" xsi:type="CharFixed" LENGTH="8" /> <FIELD ID="29" xsi:type="CharFixed" MAX_LENGTH="33" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="30" xsi:type="CharFixed" MAX_LENGTH="60" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="31" xsi:type="CharFixed" MAX_LENGTH="15" COLLATION="SQL_Latin1_General_CP1_CI_AI" /> <FIELD ID="32" xsi:type="CharFixed" LENGTH="8" /> <FIELD ID="33" xsi:type="CharFixed" LENGTH="13" /> </RECORD> - <ROW> <COLUMN SOURCE="1" NAME="CodEmpresa" xsi:type="SQLCHAR" /> <COLUMN SOURCE="2" NAME="Matricula" xsi:type="SQLCHAR" /> <COLUMN SOURCE="3" NAME="Reservado1" xsi:type="SQLCHAR" /> <COLUMN SOURCE="4" NAME="Nome" xsi:type="SQLCHAR" /> <COLUMN SOURCE="5" NAME="CodigoDR" xsi:type="SQLCHAR" /> <COLUMN SOURCE="6" NAME="DescricaoDR" xsi:type="SQLCHAR" /> <COLUMN SOURCE="7" NAME="Reservado2" xsi:type="SQLCHAR" /> <COLUMN SOURCE="8" NAME="NivelSalarial" xsi:type="SQLCHAR" /> <COLUMN SOURCE="9" NAME="DataNascimento" xsi:type="SQLCHAR" /> <COLUMN SOURCE="10" NAME="DataAdmissao" xsi:type="SQLCHAR" /> <COLUMN SOURCE="11" NAME="DataDemissao" xsi:type="SQLCHAR" /> <COLUMN SOURCE="12" NAME="EstadoCivil" xsi:type="SQLCHAR" /> <COLUMN SOURCE="13" NAME="Sexo" xsi:type="SQLCHAR" /> <COLUMN SOURCE="14" NAME="Naturalidade" xsi:type="SQLCHAR" /> <COLUMN SOURCE="15" NAME="UfNaturalidade" xsi:type="SQLCHAR" /> <COLUMN SOURCE="16" NAME="Cpf" xsi:type="SQLCHAR" /> <COLUMN SOURCE="17" NAME="CodigoBanco" xsi:type="SQLCHAR" /> <COLUMN SOURCE="18" NAME="CodigoAgencia" xsi:type="SQLCHAR" /> <COLUMN SOURCE="19" NAME="CodigoConta" xsi:type="SQLCHAR" /> <COLUMN SOURCE="20" NAME="Endereco" xsi:type="SQLCHAR" /> <COLUMN SOURCE="21" NAME="Complemento" xsi:type="SQLCHAR" /> <COLUMN SOURCE="22" NAME="Numero" xsi:type="SQLCHAR" /> <COLUMN SOURCE="23" NAME="Bairro" xsi:type="SQLCHAR" /> <COLUMN SOURCE="24" NAME="Cidade" xsi:type="SQLCHAR" /> <COLUMN SOURCE="25" NAME="UF" xsi:type="SQLCHAR" /> <COLUMN SOURCE="26" NAME="CEP" xsi:type="SQLCHAR" /> <COLUMN SOURCE="27" NAME="Telefone" xsi:type="SQLCHAR" /> <COLUMN SOURCE="28" NAME="DataFalecimento" xsi:type="SQLCHAR" /> <COLUMN SOURCE="29" NAME="CodigoEct" xsi:type="SQLCHAR" /> <COLUMN SOURCE="30" NAME="Email" xsi:type="SQLCHAR" /> <COLUMN SOURCE="31" NAME="CodigoINSS" xsi:type="SQLCHAR" /> <COLUMN SOURCE="32" NAME="DataInicioInss" xsi:type="SQLCHAR" /> <COLUMN SOURCE="33" NAME="ValorBeneficioInss" xsi:type="SQLCHAR" /> </ROW> </BCPFORMAT>
Para importar este arquivo:
0001080000983 FULANO DA SILVA 52DR/RJ 201204NM-631948030519710105200905042MRIO DE JANEIRO RJ259438637530010249600000000906RUA QUALQUER Condomínio X 0000000036BAIRRO NOVA IGUACU RJ2658205027966795 1 fulano@dominio.com.br 000001197426601200106220000000270068
São por volta de 18000 linhas.
O comando que utilizei foi este:
DECLARE @CAMINHO VARCHAR( 256 ), @FORMAT VARCHAR( 256 ), @SQL VARCHAR( 1000 ) SET @CAMINHO = '\\SERVER\Imp\teste.txt' SET @FORMAT = '\\SERVER\Imp\impcad_3.xml' SET @SQL = 'BULK INSERT PSA.ImportacaoCadastral FROM ''' + @CAMINHO + ''' WITH (FORMATFILE= ''' + @FORMAT + ''' , ROWTERMINATOR = ''' + '\n' + ''',LASTROW=100)' EXEC (@SQL)O problema é que ele importa as informações assim:
Notem que a cada nova linha ele desloca as informações dois caracteres em branco para direita.
Alguém sabe como resolver isso?
