June 18, 2003 at 8:49 am
Any help here would be appreciated.
June 18, 2003 at 10:21 am
Do you mean wich one is faster, or wich is better to use? Can you be more specific?
June 18, 2003 at 9:03 pm
I prefer using bcp specially when inserting millions of rows.
June 19, 2003 at 1:12 am
I prefer using DTS for any time ... DTS have a BCP insert task and have more sophisticated tasks which helps me in ETL processing.
****************************************
1: The question is ... "What is a MahnaMahna"?
2: The question is ... "Who cares?"
****************************************
June 19, 2003 at 7:09 am
mmm. When I posted this I'm sure it had more data in it. I'm looking for which one is better when automating task that will run after a stored procedure.
At the moment the stored procedure runs and then I run the next stage manually using BCP.
Basically the tables names are like MK030619
can have any number 1-8 and I check which ones manually. then export using bcp. But I would like to automate this and am wondering if I should us dts or BCP. I will show you the code I'm still working on (doesn't work properly yet!!!) so you can get some idea of what I'm upto.
June 19, 2003 at 7:11 am
CREATE PROCEDURE dbo.sp_predictionout AS
Declare @directory char(50), @pkzipname char(50), @tfilename char(50), @bcpcommand char(50)
Declare @name char(8), @filename char(50), @nrow int
DECLARE cName CURSOR FOR
select name from IC_PREDICTION.dbo.sysobjects
where crdate>=CONVERT(char, getdate(), 112) and xtype = 'U' and name like '_K%'
SELECT @nrow = @@rowcount
OPEN cName
FETCH NEXT FROM cName INTO @NAME
declare @output Char (40)
set quoted_identifier off
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FILENAME = 'IC_PREDICTION.dbo.'+rtrim(ltrim(@NAME))
SET @OUTPUT = 'IC_PREDICTION.DBO.'+ltrim(rtrim(@NAME))+'A'
EXEC('SELECT custno+urn+stuffermedia+surname+space(30)+firstname+
addr1+addr2+addr3+postalcode+space(7)+SUBSTRING(addr4,1,18)+sex+RTRIM(sdob)+"2400"+birthtown+RTRIM(atlas) as col1
INTO '+@output+' from '+@filename)
SET @Directory = 'Y:\NEWINNER\PREDICTIONS\uk\'
SET @tFilename = 'Y:\NEWINNER\PREDICTIONS\uk\P'+CONVERT(char, getdate(), 112)+@filename+'.txt'
SET @bcpCommand = 'bcp "SELECT * FROM '+rtrim(ltrim(@output))+' " queryout + @tfilename " '
SET @bcpCommand = @bcpCommand + @output+'" -c'
Y:\NEWINNER\PREDICTIONS\uk\P'+CONVERT(char, getdate(), 112)+@name+'.zip '
@directory
exec master..xp_cmdshell @bcpCommand
End
deallocate cname
GO
June 19, 2003 at 1:23 pm
I have used BCP similar to how you are intending to use it very successfully.
Your code however won't work, how do you suppose it will process more than the first row of the cursor without a fetch next within the loop?
June 19, 2003 at 10:23 pm
Yep!
That is true.
BCP is great and DTS is rather slow. It is however very easy to develop and gives more elements, easily "writable".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply