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?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.|||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.|||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...|||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?|||Or you could go nuts...

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|||A cursor??

Sigh. If only there was some sort of Practical Extraction and Report Language we could use... ;-)|||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...|||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.|||yes it worked, thanks.. Now I just need to understand the code.|||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...|||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?

:D