SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 lots of txt files has to be loaded (hints??)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gongxia649
So Suave

Azores
344 Posts

Posted - 08/18/2006 :  16:04:15  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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.








Edited by - gongxia649 on 08/18/2006 16:04:48

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/18/2006 :  16:14:34  Show Profile  Reply with Quote
can you concatenate all your textfiles into one?


-ec
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/18/2006 :  16:16:07  Show Profile  Visit gongxia649's Homepage  Reply with Quote
how can i do that?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/18/2006 :  16:22:48  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 08/18/2006 :  16:32:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/18/2006 :  16:35:09  Show Profile  Visit gongxia649's Homepage  Reply with Quote
i cant find information about TYPE command.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 08/18/2006 :  16:36:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/18/2006 :  18:25:16  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/20/2006 :  17:21:02  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/20/2006 :  17:23:24  Show Profile  Reply with Quote
Where did the post go ???!!!???

nevermind...

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/20/2006 :  23:30:51  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/27/2006 :  07:57:42  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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]



Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/27/2006 :  08:03:13  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote

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


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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/27/2006 :  08:23:13  Show Profile  Visit gongxia649's Homepage  Reply with Quote
if its there why do you want to drop it and recreate it?
do i have to use this validation in every SP?



Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 08/27/2006 :  08:33:06  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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

Edited by - chiragkhabaria on 08/27/2006 08:35:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000