Results 1 to 12 of 12

Thread: Bcp

  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: Bcp

    I'm trying to use the bcp utility to copy table data to a text file. I've done it for TABLE A with a file name "test.txt". Now I want to add to this txt file the data from TABLE B, without erasing the info I already have in the txt file.

    Is there anyway to do this?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not strictly by BCP. You may be able to get away with something like
    bcp db..tablea out test1.txt
    bcp db..tableb out test2.txt

    type test1.txt > test.txt
    type test2.txt >> test.txt

    I have never tried this myself, so you may be in for a ride.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One major caution. I doubt this would ever work with /n natural format. Judging by the .txt extensions on your files, you should be safe from that.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Use -c

    But why do you want to do that? Are the tables identical?

    Do they have the same number of columns? Same relative datatypes by column?

    You could create a VIEW and bcp that out....
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Originally posted by Brett Kaiser
    Use -c

    But why do you want to do that? Are the tables identical?

    Do they have the same number of columns? Same relative datatypes by column?

    You could create a VIEW and bcp that out....
    No, the tables are not identical, but since I'm saving the data to a text file I thought that wouldn't be a problem...
    Here's my situtation: I have about 8 tables (CLIENTS, PRODUCTS, INVENTORY_TRANSACTIONS, ETC.) that need to be converted to a single text file (as shown in the attachment). All the tables have different number columns, datatypes, etc. I have tried DTS and BCP but, as I explained before, they can only convert from one table at a time to different file names.

    Any ideas?
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or you could go nuts....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE TAB_LIST(TABLE_NAME sysname)
    GO
    
    INSERT INTO TAB_LIST(TABLE_NAME)
    SELECT 'Orders' UNION ALL
    SELECT 'Order Details'
    GO
    
    
    DECLARE myCursor99 CURSOR 
    FOR
    SELECT SQL FROM (
        SELECT 'SELECT CONVERT(varchar(8000),' AS SQL
    	 , a.TABLE_NAME, Null AS COLUMN_NAME, 1 AS SQL_Group, 1 AS Row_Order
          FROM INFORMATION_SCHEMA.TABLES a INNER JOIN TAB_LIST b ON a.TABLE_NAME = b.TABLE_NAME 
     UNION ALL
        SELECT 'ISNULL('+CASE  WHEN DATA_TYPE NOT IN ('char','nchar','varchar','nvarchar')
    	  	THEN 'CONVERT(char(25),'+COLUMN_NAME+')'
    		ELSE '+'+COLUMN_NAME
    	   END+','+''''+''''+')' AS SQL
    	 , a.TABLE_NAME, COLUMN_NAME, 2 AS SQL_Group, 1 AS Row_Order
          FROM INFORMATION_SCHEMA.COLUMNS a  INNER JOIN TAB_LIST b ON a.TABLE_NAME = b.TABLE_NAME 
         WHERE ORDINAL_POSITION = 1
     UNION ALL
        SELECT '+ISNULL('+CASE WHEN DATA_TYPE NOT IN ('char','nchar','varchar','nvarchar')
    		THEN 'CONVERT(char(25),'+COLUMN_NAME+')'
    		ELSE COLUMN_NAME
    	   END+','+''''+''''+')' AS SQL
    	 , a.TABLE_NAME, COLUMN_NAME, 2 AS SQL_Group, ORDINAL_POSITION AS Row_Order
          FROM INFORMATION_SCHEMA.COLUMNS a INNER JOIN TAB_LIST b ON a.TABLE_NAME = b.TABLE_NAME
         WHERE ORDINAL_POSITION <> 1
     UNION ALL
        SELECT ') AS DataRow FROM ['+ a.TABLE_NAME + ']' AS SQL
    	 , a.TABLE_NAME, Null AS COLUMN_NAME, 3 AS SQL_Group, 1 AS Row_Order
          FROM INFORMATION_SCHEMA.TABLES a INNER JOIN TAB_LIST b ON a.TABLE_NAME = b.TABLE_NAME 
     UNION ALL
        SELECT ' UNION ALL ' AS SQL 
    	 , a.TABLE_NAME, Null AS COLUMN_NAME, 4 AS SQL_Group, 1 AS Row_Order
          FROM INFORMATION_SCHEMA.TABLES a INNER JOIN TAB_LIST b ON a.TABLE_NAME = b.TABLE_NAME 
    ) AS XXX
    ORDER BY TABLE_NAME, SQL_Group, Row_Order
    
    DECLARE @sql varchar(8000), @statement varchar(8000)
    
    SELECT @SQL = 'CREATE VIEW myView99 AS '
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @statement
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	SELECT @SQL = @SQL + @statement
    	FETCH NEXT FROM myCursor99 INTO @statement
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    
    SELECT @SQL = LEFT(@SQL,LEN(@SQL)-10)
    
    EXEC(@SQL)
    GO
    	
    SELECT * FROM myView99
    GO
    /*
    EXEC master..xp_cmdshell 'bcp Northwind.dbo.myView99 out myView.txt -S<servername> -Usa -P<> -c'
    */
    
    DROP VIEW myView99
    GO
    DROP TABLE TAB_LIST
    GO
    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.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A cursor??

    Sigh. If only there was some sort of Practical Extraction and Report Language we could use.... ;-)

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey it's for an admin function..did you run the code?

    See what it does?

    Now, why would anyone want to jumble data like this I'll never know...

    Doesn't seem practical, but I thought it might be a good exercise...

    I'd keep the data in separate files?

    Want to combine them in to 1? Fine zip it baby....
    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.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The last time I saw a file like this was some sort of EDI exchange format. We needed to batch stuff up in a proto-XML type of format. It was bad.

  10. #10
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    yes it worked, thanks.. Now I just need to understand the code.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just run the sql statement by itself..

    It's dynamic sql that creates a view, the view transforma all of the columns from each table 1 to 1 varchar(8000) column

    That way all of the data types are the same, and so also the number of columns (1)

    Since those are the rules of a view..

    # Columns must be the same and datatypes as well...

    Then you just bcp out the view, which is a union of everytable that was loaded in to the work table tab list...

    I set all non char data to char(25). Mostly because I think the biggest a date field can be is 25...

    So being the lazy dba that I am...I didn't want to remeber what size I made what datatype...


    There's a caution to this tale: Don't use this type of stuff in a production system...if I had to I would gene the statement and install it with a script...
    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.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by diegocro
    yes it worked, thanks.. Now I just need to understand the code.
    Was there a hint of suprise in that post?

    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
  •