Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Upload data in text files

    Hello experts,

    I’ve a task to accomplish which is totally new for me. I’ve 3 tables which are holding the data that I need. Now I’ve been asked to produce 3 text files which will hold this data. Can anybody point me in the right direction from where I should start and how I can accomplish it?

    Also I’ll need to perform this procedure at the end of every month. Move data to text files and purge data from database. Should I use SSIS?

    Many thanks in advance
    Last edited by asyed01; 12-07-09 at 17:50.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No...use BULK OUTSERT

    Oh damn M$ didn't think that would be a good feature...WHY?

    Because they'd have to Layoff an entire team building SSIS or DTS

    In any case....

    Use bcp to unload the data

    How do you want the output to look?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wait...your topic say upload, but your note say unload

    Are you a woman?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work.
    Code:
    if exists (select 1 from  sysobjects where id = object_id('dbo.P_KSZ2XMLs') and type = 'P')
    	DROP PROCEDURE dbo.P_Table2CSV
    go
    
    CREATE PROCEDURE dbo.P_Table2CSV (
    	@ServerName		NVARCHAR(20),
    	@FullTableName	NVARCHAR(200),	-- table or view, fully specified eg 'AdventureWorks.dbo.MyTable'
    	@FilePath		NVARCHAR(100),
    	@FileNamePrefix	NVARCHAR(100)
    	)
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	DECLARE @FullFileName varchar(250)
    	DECLARE	@TimeStrFileName varchar(13)
    	DECLARE	@SQLStr varchar(500)
    	DECLARE @bcpCommand varchar(2000)
    
    	SELECT 	@TimeStrFileName = RIGHT(RTRIM(REPLACE(CONVERT(VARCHAR(24), GetDate(), 113) ,':', '_')),12)
    
    	SET @FullFileName = @FilePath + @FileNamePrefix + '-' + @TimeStrFileName + '.txt'
    --PRINT '@FullFileName = ' + @FullFileName
    	SET @SQLStr = 'SELECT * FROM ' + @FullTableName
    --PRINT '@SQLStr = ' + @SQLStr
    	SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FullFileName + '" -t, -T -c -S ' + @ServerName
    --  -c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
    --  -t override the field terminator with ","
    --  -T use a trusted connection. Note that U P may be used for username/password
    --  -S connect to this server to execute the command
    
    --PRINT '@bcpCommand = ' + @bcpCommand
    	EXEC master..xp_cmdshell @bcpCommand
    
    	SET NOCOUNT OFF
    
    END	--  PROCEDURE
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well if your surface area is configured correctly
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •