BCP or DTS

  • Any help here would be appreciated.

  • Do you mean wich one is faster, or wich is better to use? Can you be more specific?

  • I prefer using bcp specially when inserting millions of rows.

  • 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?"
    ****************************************

  • 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.

  • 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

  • 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?

  • 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