ansaurus

Question

export query results into CSV file issue in SQL Server

Answer 1

+3 A:

When you click "Save as" and you are prompted to enter a filename, drop down the box below the filename and select "Text (Tab delimited)"

Mitch Wheat 2009-08-07 10:14:32

Thanks, it works in SQL Server 2008, and I have tried in SQL Server 2005 no such feature?

George2 2009-08-07 10:36:15

Answer 2

+2 A:

If you really want the \t delimiter write a query like this

select 
  Cast(PersonCode as varchar(50)) + '\t'
  + Cast(PersonReference as varchar(50))    
from People

The casts are just incase you aren't working with varchar types, but you don't need them if you are. Run this query with results to text and just paste the result into notepad or similar.

Mark Dickinson 2009-08-07 10:17:17

You don't even need to paste. Using results to grid, just right click and save results as, using all files as the file type, to allow you to save as a txt file or whatever you want.

Mark Dickinson 2009-08-07 10:23:14

@ Mark Dickinson: isn't that what I said in my answer?! :)

Mitch Wheat 2009-08-07 10:33:15

Not working for me since I have binary type if case is not used. Here is the error message. My further question, if I cast binary to varchar, then cast back, is it ensured that originial value of binary is restored?Msg 402, Level 16, State 1, Line 1The data types nvarchar and varbinary(max) are incompatible in the add operator.

George2 2009-08-07 10:43:43

@Mitch: I don't have that option in my save results as. I just get Export File (csv), or All files (*.*). Maybe I misunderstand the '\t' in the question, does that mean tab? Anyway my example means you could use War and Peace for a delimiter if you want so I still think its good :)

Mark Dickinson 2009-08-07 10:45:15

@Mitch, I'm on 2005 in the office, that might explain the difference.

Mark Dickinson 2009-08-07 10:48:09

The columns in the table contains, uniqueidentifier type, int type, nvarchar type, datetime type and varbinary(max) type, I am not sure whether cast them all to nvarchar type then cast back will restore the exact original value?

George2 2009-08-07 10:48:15

@George2: Casting binary to a char type should be ok, it would loose any leading zeros if you cast to numeric type.

Mark Dickinson 2009-08-07 10:49:45

"if you cast to numeric type" -- do you mean cast from numeric type to nvarchar type? :-)

George2 2009-08-07 10:52:07

I meant, if you cast binary to nvarchar, you should be ok. I'd probably go for a varchar(max) from a varbinary(max)

Mark Dickinson 2009-08-07 10:54:42

Thanks Mark, 1. I have tested your solution and it works -- at least no error from SQL Server Management Studio. I have casted int/datetime/varbinary(max)/uniqueidentifier to nvarchar(max), is that fine? No data lost? 2. Another question is how to restore such data to another table of the same schema (I found there is only one column when we use cast and +)?

George2 2009-08-07 10:58:33

You need a split function, if you look for them on google there are a few user defined functions. These are table valued functions that split the string and return the results as a queryable table. I'll have a quick snoop and post a link.

Mark Dickinson 2009-08-07 11:00:48

Here's a starter http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648, you may obviously need to get this to return more than two columns. You run the create function script then you can query it like a view or a table.

Mark Dickinson 2009-08-07 11:03:33

Thanks Mark, how about your answer and comment to this question -- "Cast int/datetime/varbinary(max)/uniqueidentifier to nvarchar(max), is that fine? No data lost?"? :-)

George2 2009-08-07 11:11:30

If you are able to see both tables in your management studio. What's to stop you just writing an Insert...Select query. It doesn't matter if the tables are in different dbs as long as you have permission to use both.

Mark Dickinson 2009-08-07 11:12:03

Re: casting, yeah should be fine (no warranty on this though).

Mark Dickinson 2009-08-07 11:13:06

Hi Mark, the two tables are from two database servers. I have permission, and currently I am using linked server to bring the remote database server to the local database server's Management Studio. Can we write insert from one table inside one database server to another table of another database server?

George2 2009-08-07 11:15:54

Try writing something like select * from sourcedb.user.sourceTable . If it works try writing Insert into destDb.user.destTable select * from sourcedb.user.sourceTable . user is usually dbo. Being explicit about the columns gives you change to cope with any schema changes.

Mark Dickinson 2009-08-07 11:21:54

Hope that helps, I'm off to the chippy now :)

Mark Dickinson 2009-08-07 11:25:34

It helps Mark, thanks! I have marked your reply as answered.

George2 2009-08-07 13:02:39

ansaurus

tags:

views:

answers:

export query results into CSV file issue in SQL Server

related questions