Exporting result set to a file

Hi All

I have written a sp and I would like to export he result set to a file.

Is there a bit of script I can add to do this for me?

Many Thanks

Rich

Code Snippet

exec master..xp_cmdshell 'bcp "select * from <mytable>" queryout c:\<myFile.txt> -S<Servername> -U<myUserId> -P<myPassword>'

replacing all the <...> fields with your values

|||Thankssql

Exporting data between databases

Just starting out with MSSQL 2000 and have a bit of a problem.
I have a remote database that is of the same design of a local database. What I want to do is update one particular field of each record in my local database with data from the remote one. Of course the primary key of each of the records from the remote dbase and the local dbase must match (as even though the remote database has the most up-to-date information, it has more records in it than my local one (but I do not require the additional records).

How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?

Any help would be appreciated!

Thanks.If you have ACCESS 2000 or 2002 you can create two linked tables.

One to each table and then use access to run your update, then you can run you insert of the additional fields at the same time.|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records? And using Inner Join in the update statement.

Best regards!|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records?

Best regards!

Exporting BIT datatypes?

Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END|||select cast(colBit as int) as colInt|||I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END

That is a darn good idea - but I dont have the privs. to make a view :-/
Edit: this is all going straight into oracle.|||So what format is the output in?|||So what format is the output in?

If I let DTS handle the datatype - it gives it the datatype NUMBER and the value 0 or -1.

If I manually change the columns datatype to char, I get T or F.
edit:this is all going directly into oracle.|||You know you can just type the sql in to the source in DTS

How much data are we talking about...|||You know you can just type the sql in to the source in DTS

How much data are we talking about...

This happens in a few tables...We're talking over 10,000 rows.|||Does Oracle even have a bit datatype?

What version we talking about?|||it most certainly does not. 9i.
if i could use number, and it didn't automatically turn "1" into "-1" this wouldn't be a big deal but alas...|||How about the ABS function? Would that work?|||How about the ABS function? Would that work?

hmmm yes, that'd work on the front end for the conditonal statements but it'd still require me to change that all over the application unless there was some way to run that during DTS...(which there may be? I'm not too familiar with SQL server or DTS)

Export table containing BIT data

I'm trying to export a table that contains a bit field into a text file to be used for a bulk insert into another database.

when i export the data from SQL enterprise manager, the bit field is exported as the text TRUE or FALSE ?!!!

this data then cant be bulk inserted as its not a BIT anymore.

how can bit fields be properly exported as 1 or 0 ?Are you doing this in a DTS Package? Use CAST or CONVERT on the BIT field and change the output to an INTEGER or CHAR(1). They are basically the same thing but in this scenario either or is fine; since BIT transformation are not explicit and generally require no intervention to get the value of 1 or 0, however if I'm not mistaken, using a Text ODBC driver causes BITs to be converted to TRUE/FALSE. That's why I asked if you are using DTS, it's ODBC driven. If you are using DTS, you will need to write a query in the Source tab using one of the functions below.

Using CAST

CAST(bYourBit AS INTEGER) AS bYourBit

Using CONVERT

CONVERT(CHAR(1),bYourBit) AS bYourBit