| Author |
 Topic  |
|
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/18/2006 : 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.
|
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
|
can you concatenate all your textfiles into one?
-ec |
 |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/18/2006 : 16:16:07
|
| how can i do that? |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 08/18/2006 : 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
USA
35007 Posts |
Posted - 08/18/2006 : 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
Azores
344 Posts |
Posted - 08/18/2006 : 16:35:09
|
| i cant find information about TYPE command. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/18/2006 : 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
Sweden
3279 Posts |
Posted - 08/18/2006 : 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
Sweden
3279 Posts |
Posted - 08/20/2006 : 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
Sweden
3279 Posts |
Posted - 08/20/2006 : 17:23:24
|
Where did the post go ???!!!???
nevermind...
 |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/27/2006 : 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
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/27/2006 : 08:03:13
|
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 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/27/2006 : 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
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 08/27/2006 : 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 |
Edited by - chiragkhabaria on 08/27/2006 08:35:01 |
|
|
| |
Topic |
|
lots of txt files has to be loaded (hints??)
