|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
BCP with a query in a file, or SQLCMD outputting tab-delimited?I need to do a few simple tab-delimited extracts from MS SQL Server
2005. The extracts are going to be relatively simple table joins, but still the SQL for the query could be a few hundred characters long so I'm a little uncomfortable specifying a SQL command of that length on the BCP command line, I'd be a lot happier putting the SQL query into a file. Can this be done in BCP, taking the SQL query from an input file? Alternatively, what set of options and switches / commands etc. should I use to make SQLCMD output a tab-delimited file with no column headers? I'm doing something similar from an Oracle database, and their SQL*Plus command-line utility has commands such as SPOOL to specify an output file within the .sql script, and also SET HEADING OFF, SET DELIMITER, etc. - are there analagous commands within SQLCMD? Phil Hibbs. On 16 Oct, 10:27, PhilHibbs <sna***@gmail.com> wrote:
> Alternatively, what set of options and switches / commands etc. should Oh, it doesn't have to be tab-delimited, it can be CSV.> I use to make SQLCMD output a tab-delimited file with no column > headers? Phil Hibbs. Phil,
For BCP you could create a view in your database that handles all of the join logic, then simply bcp out from the view. (This is what I would probably do.) For SQLCMD the Books Online give all the parameters for SQLCMD. http://technet.microsoft.com/en-us/library/ms162773.aspx Particularly look at -h Controls the headers (-1 means no header) -s Sets the column separator RLF Show quote "PhilHibbs" <sna***@gmail.com> wrote in message news:1192526877.427025.226540@t8g2000prg.googlegroups.com... >I need to do a few simple tab-delimited extracts from MS SQL Server > 2005. The extracts are going to be relatively simple table joins, but > still the SQL for the query could be a few hundred characters long so > I'm a little uncomfortable specifying a SQL command of that length on > the BCP command line, I'd be a lot happier putting the SQL query into > a file. Can this be done in BCP, taking the SQL query from an input > file? > > Alternatively, what set of options and switches / commands etc. should > I use to make SQLCMD output a tab-delimited file with no column > headers? I'm doing something similar from an Oracle database, and > their SQL*Plus command-line utility has commands such as SPOOL to > specify an output file within the .sql script, and also SET HEADING > OFF, SET DELIMITER, etc. - are there analagous commands within SQLCMD? > > Phil Hibbs. > On Oct 16, 1:44 pm, "Russell Fields" <russellfie***@nomail.com> wrote: It's not my database. Can't create anything.> For BCP you could create a view in your database that handles all of the > join logic, then simply bcp out from the view. Phil Hibbs. Phil, Then go with SQLCMD. It works fine. - RLF
Show quote "PhilHibbs" <sna***@gmail.com> wrote in message news:1192542761.505192.61550@y27g2000pre.googlegroups.com... > On Oct 16, 1:44 pm, "Russell Fields" <russellfie***@nomail.com> wrote: >> For BCP you could create a view in your database that handles all of the >> join logic, then simply bcp out from the view. > > It's not my database. Can't create anything. > > Phil Hibbs. > On 16 Oct, 15:41, "Russell Fields" <russellfie***@nomail.com> wrote: How do I suppress the blank line followed by "(nnn rows affected)" at> Phil, Then go with SQLCMD. It works fine. - RLF the end? I've read through the page you linked but I can't see it. It truly baffles me how hard it is to get data out of a table into a CSV file, either in MS SQL or in Oracle. Having to do '"'+TRANSLATE(fieldname, '"','""')+'"' on all character fields manually is just awful, are you sure there isn't an easier way of doing this? Phil Hibbs. Phil,
Here is a command line that I used to test (all on one line, of course): sqlcmd -S MyServer -E -Q"SET NOCOUNT ON SELECT * FROM MASTER.DBO.SYSDATABASES" -h -1 -s" " -o Result.Txt The SET NOCOUNT ON turns off the (nnn rows affected) message. The -s" " in the command line contains a tab character, but it may not come over in the cut and paste. Just type a tab key between the "". This puts tabs between each column. If your data does not contain tab characters, then this may work for you. According to the Books Online, any 8-bit character can be a column separator. I used -Q to test quickly, but you could have a query file and use -i to input it into SQLCMD. If you need to create the beginning and ending field delimiters, then you might revisit BCP and look at using the format file definitions which manage this. Format files have their own complications, of course. Hope it helps, RLF Show quote "PhilHibbs" <sna***@gmail.com> wrote in message news:1192551667.069212.139060@i38g2000prf.googlegroups.com... > On 16 Oct, 15:41, "Russell Fields" <russellfie***@nomail.com> wrote: >> Phil, Then go with SQLCMD. It works fine. - RLF > > How do I suppress the blank line followed by "(nnn rows affected)" at > the end? I've read through the page you linked but I can't see it. > > It truly baffles me how hard it is to get data out of a table into a > CSV file, either in MS SQL or in Oracle. Having to do > '"'+TRANSLATE(fieldname, '"','""')+'"' on all character fields > manually is just awful, are you sure there isn't an easier way of > doing this? > > Phil Hibbs. > > Thanks for all your help so far, I'm really getting somewhere now,
using SQLCMD. All the null values are coming out as the text string NULL, is there any way to resolve this other than to use ISNULL around all columns that might contain null values? Phil Hibbs. Phil,
I do not know of a way to do that using SQLCMD. (In BCP a NULL is simply nothing at all betwee the column delimiters.) RLF Show quote "PhilHibbs" <sna***@gmail.com> wrote in message news:1192637427.026565.119780@i13g2000prf.googlegroups.com... > Thanks for all your help so far, I'm really getting somewhere now, > using SQLCMD. > > All the null values are coming out as the text string NULL, is there > any way to resolve this other than to use ISNULL around all columns > that might contain null values? > > Phil Hibbs. > PhilHibbs wrote:
> On 16 Oct, 15:41, "Russell Fields" <russellfie***@nomail.com> wrote: CSV is an awful format anyway, if you ask me. Much prefer TSV.>> Phil, Then go with SQLCMD. It works fine. - RLF > > How do I suppress the blank line followed by "(nnn rows affected)" at > the end? I've read through the page you linked but I can't see it. > > It truly baffles me how hard it is to get data out of a table into a > CSV file, either in MS SQL or in Oracle. Having to do > '"'+TRANSLATE(fieldname, '"','""')+'"' on all character fields > manually is just awful, are you sure there isn't an easier way of > doing this? On 17 Oct, 20:03, Ed Murphy <emurph***@socal.rr.com> wrote:
> CSV is an awful format anyway, if you ask me. Much prefer TSV. Doesn't make a lot of difference in my opinion, the main problem thatI have with tab separated files is that my code editor by default expands tabs into spaces, so I have to turn that off or I mess up my files if I edit them. Since our data frequently has tab characters in it (and thus needs quotes around fields in either format), it makes little difference whether the separator is a comma or a tab. It's just a character. Phil. PhilHibbs (sna***@gmail.com) writes:
> On Oct 16, 1:44 pm, "Russell Fields" <russellfie***@nomail.com> wrote: Could create the view in tempdb.>> For BCP you could create a view in your database that handles all of the >> join logic, then simply bcp out from the view. > > It's not my database. Can't create anything. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics