help Bulk Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help Bulk Insert

Author

Topic

vlacerda
Starting Member

7 Posts

Posted - 2009-04-08 : 08:10:20

hy guys!!
I have a problem....because my process are many tall
I need import this example:

20004121770000001002R00002000140595000000000000000000000000
20004121770000001002E54104000469467000000000000003000025365
20004121770000001002R54004000372545003000025363000000000000
20004121770000001002R50002000000999000000000000000000000000
20004121770000001002G55002000000000000000000000000000000000
20004121770000001002I54006000000000000000000000000000000000
20004121770000001002R00022000007900000000000000000000000000

I did try import for Bulk insert and after finish i using "while" for delimited my file...but this process not correct.
I need help for delimited my file in moment I using Bulk insert, example:

column01 column02 column 03 column04 column05 ...
char(1,1) char(2,5) char(6,10) char(16,3) char(19,5) ....

Today I use this code for Import and ajust my file:

drop table Sintetico

CREATE TABLE Sintetico
(
id varchar(30),
Conta varchar(30),
Datavencimento varchar(30),
Qtdedeplanos varchar(30),
Saldototal varchar(30),
SaldototalSinal varchar(30),
Atraso varchar(30),
[Status] varchar(30),
Limitedecredito varchar(30),
LimitedecreditoSinal varchar(30),
LOGO varchar(30),
LimitedecreditoSaque varchar(30),
LimitedecreditoSaqueSinal varchar(30),
Statusdeinibicao varchar(30)
)

drop table Analitico

CREATE TABLE Analitico
(
id varchar(30),
Conta varchar(30),
Numeroplano varchar(30),
Saldoinicial varchar(30),
SaldoinicialSinal varchar(30),
SaldoRotativo varchar(30),
SaldoRotativoSinal varchar(30)
)

--create index idx_sintetico on Sintetico (conta)
--create index idx_Analitico on Analitico (conta)

delete from Fonte
delete from Analitico
delete from Sintetico

BULK INSERT Fonte
FROM 'D:\Valter\PEGSAL\PEGPSAL_030409.txt'
PRINT 'Relatorio Importado - Sintetico'

-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
DECLARE @count int
set @count = (SELECT count(*) from Fonte)
--declare @valida int
-- set @valida = (select substring(min(Dados),1,1) from Fonte_Sintetico where substring(Dados,1,1) <> 0)
declare @count1 int set @count1 = 1
--delete from Sintetico
WHILE (@count1 <= @count )
BEGIN

INSERT INTO Sintetico
select
substring(Dados,1,1) as Id,
substring(Dados, 2, 19) as conta,
substring(Dados, 30, 7) as Datavencimento,
substring(Dados, 37, 3) as Qtdedeplanos,
substring(Dados, 112, 9) as Saldototal,
substring(Dados, 121, 1) as SaldototalSinal,
substring(Dados, 132, 4) as Atraso,
substring(Dados, 139, 1) as [Status],
substring(Dados, 140, 11) as Limitedecredito,
substring(Dados, 151, 1) as LimitedecreditoSinal,
substring(Dados, 179, 3) as Logo,
substring(Dados, 182, 13) as LimitedecreditoSaque,
substring(Dados, 195, 1) as LimitedecreditoSaqueSinal,
substring(Dados, 233, 1) as Statusdeinibicao
from Fonte
--CONTINUE
--set @count1 = @count1 + 1

-- IF @count1 = @count

--BREAK
END
PRINT 'Load Sintetico';

Sorry, my english is not very well!!

No more Valter Lacerda, and Ty!

Valter Lacerda
(55)+ 11 83009925

I need Power Guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 08:26:41

Have you tried using a format file?
This automatically splits the file for you into columns.

E 12°55'05.63"
N 56°04'39.26"

vlacerda
Starting Member

7 Posts

Posted - 2009-04-08 : 09:08:10

quote:
Originally posted by Peso

Have you tried using a format file?
This automatically splits the file for you into columns.

E 12°55'05.63"
N 56°04'39.26"

Ty...
Do you have one example for me...
I did look in google about format fil but I didnt understand...

Valter Lacerda
(55)+ 11 83009925

I need Power Guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 09:17:03

Books Online is your friend.
http://msdn.microsoft.com/en-us/library/ms188365(SQL.90).aspx

E 12°55'05.63"
N 56°04'39.26"

vlacerda
Starting Member

7 Posts

Posted - 2009-04-08 : 09:25:26

ty i will study....

Valter Lacerda
(55)+ 11 83009925

I need Power Guys!

vlacerda
Starting Member

7 Posts

Posted - 2009-04-08 : 09:56:48

ok i did read many posts in books online.
I need Know what kind TERMINATOR="?????" i use in my xml for specify my field in my file?

TY

Valter Lacerda
(55)+ 11 83009925

I need Power Guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 10:00:07

XML?

E 12°55'05.63"
N 56°04'39.26"

vlacerda
Starting Member

7 Posts

Posted - 2009-04-08 : 10:04:40

xml or fmt correct?
what kind i use in Terminator in my file FORMATFILE for specify my layout in my file?

do you know.....

do you use formatfile anytime?

Valter Lacerda
(55)+ 11 83009925

I need Power Guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources