lots of txt files has to be loaded (hints??)

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

General SQL Server Forums

New to SQL Server Programming

lots of txt files has to be loaded (hints??)

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-18 : 16:04:15

have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.

just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.

say:
exec SP_loadTXT txt1

or should i use bulk insert?

any approaches are fine. any suggestions are fine too.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-18 : 16:14:34

can you concatenate all your textfiles into one?

-ec

gongxia649
So Suave

344 Posts

Posted - 2006-08-18 : 16:16:07

how can i do that?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-18 : 16:22:48

there are a bunch of ways you can do this.

the easiest would to just use the TYPE command. The COPY command can also be used. If you google around you might find some scripts to automate this.

TYPE filename1 >> filename2

do that for all you files and then load one file. btw, I would work on copies of these files and/or concatenate all your files to a new file. This way you don't actually modify any of your existing files.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-18 : 16:32:47

Here's how I concatenate files. You can use wild card characters for the files to find.

type C:\temp\*.txt > C:\SomeFolder\ConcatenatedFile.txt

The type command concatenates the files alphabetically.

Tara Kizer

gongxia649
So Suave

344 Posts

Posted - 2006-08-18 : 16:35:09

i cant find information about TYPE command.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-18 : 16:36:35

quote:
Originally posted by gongxia649

i cant find information about TYPE command.

It's a command line tool. Open a cmd window, then type:

type.exe /?

or

type /?

to see its options.

Tara Kizer

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-18 : 18:25:16

say:
exec SP_loadTXT txt1

------------------------------------

-- @txt1 is the path to the file to load

something like:

declare @cmd nvarchar(4000)
set @cmd = 'BULK INSERT database.dbo.table FROM ''>filename<'''
set @cmd = replace(@cmd,'>filename<',@txt1)

-- print @cmd

exec(@cmd)

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-20 : 17:21:02

I meant like this:

delete address

declare @cmd nvarchar(4000)
declare @txt1 nvarchar(4000)

set @txt1 = 'c:\1.txt'

set @cmd = 'BULK INSERT address FROM ''>filename<'''
set @cmd = replace(@cmd,'>filename<',@txt1)

print @cmd

exec(@cmd)

select * from address

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-20 : 17:23:24

Where did the post go ???!!!???

nevermind...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-20 : 23:30:51

see
http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gongxia649
So Suave

344 Posts

Posted - 2006-08-27 : 07:57:42

i have a question

why in some SP i always see something like this

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ImportFiles]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-27 : 08:03:13

[code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ImportFiles]
[/code]

The Code means that, if the Stored Procedure is already there in the database then Drop the procedure and recreate the complete new version of that procedure.

If you dont put this validation, then it will give you error if the procedure is already there and you are trying to create the same procedure.

Chirag

gongxia649
So Suave

344 Posts

Posted - 2006-08-27 : 08:23:13

if its there why do you want to drop it and recreate it?
do i have to use this validation in every SP?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-27 : 08:33:06

quote:

do i have to use this validation in every SP?

ITs good to put this kind of validation, so that it doesnt generate any errors, if the stored procedure is already there.

its not complusory though.

if you are creating the script from Enterprise Manager then, for every object, this kind of validation is there.
so if you see its normal standard followed by most of the developers around the world, while sending the database objects scripts.

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources