Another SQL Question

How do I select data from a table in a format where I can
BCP it. The columns have to be separated by colons

Thanks
0
Charles
6/21/2005 7:30:33 PM
sybase.ase.general 8655 articles. 0 followers. Follow

6 Replies
522 Views

Similar Articles

[PageSpeed] 5
Get it on Google Play
Get it on Apple App Store

I want a query that will retrieve columns from a table in a
format where I can BCP it into another table. The columns
should be separated by colons:

Thanks

> How do I select data from a table in a format where I can
> BCP it. The columns have to be separated by colons
>
> Thanks
0
Charles
6/21/2005 8:05:59 PM
Use bcp with the -t option to specify a different column delimiter.

You can also create a view and then bcp out the view and use the -t
option on the bcp.

Bob

On 21 Jun 2005 13:05:59 -0700, Charles Bonsu wrote:

>I want a query that will retrieve columns from a table in a
>format where I can BCP it into another table. The columns
>should be separated by colons:
>
>Thanks
>
>> How do I select data from a table in a format where I can
>> BCP it. The columns have to be separated by colons
>>
>> Thanks

0
Robert
6/21/2005 9:40:53 PM
I am creating test data to be be bcp'd into tables, but I
need to first select a record from some table and bcp the
data into a table
 I want a query that can select columns a b c d e ......
into a flat file with the format:

test:charles:bonsu:334556666:atlanta:

Charles
> Use bcp with the -t option to specify a different column
> delimiter.
>
> You can also create a view and then bcp out the view and
> use the -t option on the bcp.
>
> Bob
>
> On 21 Jun 2005 13:05:59 -0700, Charles Bonsu wrote:
>
> >I want a query that will retrieve columns from a table in
> a >format where I can BCP it into another table. The
> columns >should be separated by colons:
> >
> >Thanks
> >
> >> How do I select data from a table in a format where I
> can >> BCP it. The columns have to be separated by colons
> >>
> >> Thanks
>
0
Charles
6/22/2005 1:20:54 AM
So, as Bob had suggested, you could do the following:

create view bcp_view
as
select a,b,c,d,e,null as 'filler'
from	my_table
go

Then bcp out from bcp_view with '-c -t":"' to give you ":" as a field 
delimiter.

The addition of the 'null' to the select list is to insure you end up 
with a ":" at the end of your data line:

	a:b:c:d:e:

Charles Bonsu wrote:

> I am creating test data to be be bcp'd into tables, but I
> need to first select a record from some table and bcp the
> data into a table
>  I want a query that can select columns a b c d e ......
> into a flat file with the format:
> 
> test:charles:bonsu:334556666:atlanta:
> 
> Charles
> 
>>Use bcp with the -t option to specify a different column
>>delimiter.
>>
>>You can also create a view and then bcp out the view and
>>use the -t option on the bcp.
>>
>>Bob
>>
>>On 21 Jun 2005 13:05:59 -0700, Charles Bonsu wrote:
>>
>>
>>>I want a query that will retrieve columns from a table in
>>
>>a >format where I can BCP it into another table. The
>>columns >should be separated by colons:
>>
>>>Thanks
>>>
>>>
>>>>How do I select data from a table in a format where I
>>
>>can >> BCP it. The columns have to be separated by colons
>>
>>>>Thanks
>>
0
Mark
6/22/2005 2:59:50 AM
> I am creating test data to be be bcp'd into tables, but I
> need to first select a record from some table and bcp the
> data into a table
>  I want a query that can select columns a b c d e ......
> into a flat file with the format:
>
> test:charles:bonsu:334556666:atlanta:
>

You can create a view and use bcp, as others have suggested,
or you might consider installing and using sqsh where you 
can produce BCP format output directly:

1> select ... from foo where bar
2> go -m bcp > my_bcp_file.out

Michael
0
mpeppler
6/22/2005 6:26:33 AM
Ah..

select
    col1 + ':' + col2 + ':' + ... etc.,
into
    tempdb..newtable
from
    currenttable

Like that?

Of course, I'd go with the bcp option specifying delimiters... it'd be the
easiest & fastest.
Jeff

<Charles Bonsu> wrote in message news:42b8bcf6.7af0.1681692777@sybase.com...
> I am creating test data to be be bcp'd into tables, but I
> need to first select a record from some table and bcp the
> data into a table
>  I want a query that can select columns a b c d e ......
> into a flat file with the format:
>
> test:charles:bonsu:334556666:atlanta:
>
> Charles
> > Use bcp with the -t option to specify a different column
> > delimiter.
> >
> > You can also create a view and then bcp out the view and
> > use the -t option on the bcp.
> >
> > Bob
> >
> > On 21 Jun 2005 13:05:59 -0700, Charles Bonsu wrote:
> >
> > >I want a query that will retrieve columns from a table in
> > a >format where I can BCP it into another table. The
> > columns >should be separated by colons:
> > >
> > >Thanks
> > >
> > >> How do I select data from a table in a format where I
> > can >> BCP it. The columns have to be separated by colons
> > >>
> > >> Thanks
> >


0
Jeffrey
6/22/2005 1:57:22 PM
Reply:

Similar Artilces:

GENERAL SQL SERVER: Question about the Script that setups the SQL Server Database.
This is the first time I have created a script for Sql Server 2000, and I have run into a few syntax ereas that I am not familar with. Bellow is a section of the script from the Portal server packages. At the end of the sample, is "ON [PRIMARY], Can anyone explain to me what this does and why the primary key isnt setup inside the code itself? I am basically trying to take parts of the script and incorrporate it into my own script and hate added syntax that I dont understand, makes debugging a real pain in the future. CREATE TABLE [dbo].[Portal_Announcements] ( [ItemID] [in...

general Sql question
Given the following tables Table1 --------- labnumber name doctor Table 2 --------- labnumber ailmentcode (hold 0 to many ailments for records in table 2) How can I get all the records in table 1 that have ailments (related in table 2 by labnumber) 1, 2, 3, 4, 5, 6, 7, 8, and 9. Thanks in advance Blake select table1.* from table1, table2 where table1.labnumber = table2.labnumber and table2.ailment in (1,2,3,4,5,6,7,8,9) Instead of the IN clause you could code and table2.ailment between 1 and 9 or even and table2.ailment >= 1 and table2.ailme...

general SQL question
--------------7EC95B4FEE051BD577C3D038 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a general SQL question. If there is a two-column table, e.g., id keyword -- ------ 1 k1 2 k2 1 k2 2 k3 (1) Is it possible to retrieve the values for id 1 by saying "select id from foo where keyword=k1 and keyword=k2" The above returns nothing. Since we are comparing against a single column (keyword), this doesn't seem possible to do the "and". (2) If...

General SQL Question
 I am trying to create a query that will return the matching image name from one table or displays a null value for that column if there is no matching row. So for example I want to go through all the event table rows and match up the row information with image_name from Image Gallery table. If nothing matches up from that row I want a null value displayed. I been working on this for a while and all I manage to come up with is only returning rows that have matching IDs from Image Gallery and Event.  --------Image Gallery----------Image Name |  EventIDBaseball  &...

Another SQL script question.
Hey all, How do i make sure that the local machine aspnet user account has privilages after installing my sql script on a local machine. ( this script gets run during my vb.net setup project. ) i was thinking about adding the user to the sql script its self. but i am unsure how to do this. if anyone could help i would be greatly appreciative. TIA -Chris-I am one of those! Check out this link. The link is for MSDE but applies to Sql Server too. It shows the syntax for adding new users to a database server and granting them access to specific databases. Thanks for you rep...

Another EAServer general question
We are considering purchasing software from a consulting company written in JAVA. I would like to require that it run on EAServer. Is there any good reason that would not be possible? If not EAServer wouldn't that software have to run either locally on a machine or if I want multiple users to use the same software on some app. server like Weblogic or Websphere of EAServer? If it's straight Java, it may not run in a J2EE container. (It should, and you could probably ask for it, but it's not a requirement). If however, the Java software is J2EE compliant (which you *shou...

General SQLA/SQL question
I am curious if there is a way to create a temp table with the exact same structure as an existing table in one step. In other words, I don't want to either create the table by selecting the type and name of column from the systables or to explicitly create the table ahead of time and then populate it by selecting from the existing table. I am looking for something that would be similar to CREATE TEMPORARY TABLE X_TEMP AS SELECT * FROM X. I know that is not the correct syntax, but you get the idea. TIA, JM. Now this would be nice! Don't forget the 'on commit...

Yet another SQL question
Using SQL Anywhere 5.5.04 Is it possible to do the following update tableA set A= , B= (select aa,bb from tableB where ...) the where clause in the select statement is quite complex and I don't want the overhead of update tableA set A=(select aa from tableB where ...) , set B=(select bb from tableB where ...) Thanks TPS You can use UPDATE FROM e.g. update tableA from tableA, tableB set tableA.A=tableB.aa, tableA.B=tableB.bb where ... Bye Thomas Tom Starkweather <scientex@aros.net> schrieb im Beitrag <36...

db-sql general question
I have some common questions reg sql and db operataions..i have googled on this and found the overwhelming information,i understand some and confused abt someother ..if someone can give me all the answers at one place briefly,i wud really appreciate that...i am usign SQL server 2005 and i have DAL(ADO.NET) BLL set up in my  C#  web application 1-whats the significance of 'RETURN' in SQL? i mean,what will happen if i don't have it .When does having RETURN  matter at the end of SQL? 2-i am using the OUTPUT parameter,to get the id of inserted row.However in case of...

General Question about SQL efficiency
I have a website that is probably going to hold a sizable amount of data.  The data will be specific to groups of users based on login credentials.  Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database?  Any thoughts on the topic would be appreciated. ThanksJayMan I'm not quite sure what you consider a sizable amount of data, but one database should be fine.  If you feel that the database is beginning to act slow you can always partition tables, re-evaluate indexes set up o...

General question about inline sql
I'm executing inline sql (PB 10.1) against a MS SQL server database as per the documentation and i'm getting null in the 'into' value. The select will work in the DB painter. Have there been changes in the last few versions of PB regarding this. I've never had this problem before. Mark wrote: > I'm executing inline sql (PB 10.1) against a MS SQL server > database as per the documentation and i'm getting null in > the 'into' value. The select will work in the DB painter. > Have there been changes in the last few versions of PB > r...

general question for ASP and SQL database
Hi everyone, I've wrote a ASPX page that reads data from my SQL database. The SQL database reside in virtual window server 2003 that has put on the company's network. Everything works fine when the virtual server is on company's network. And now I unjoined the virtual server from the network and ASPX page doesn't work anymore. It seems because it can not get data from SQL server. Is there ways to make ASPX and my SQL server work fine without putting the server on the company's network? Should I setup a physical server and put SQL ser...

Simple SQL Question
Looking for you Classicly Trained Database Artists to give me Your opinion on this scenario: - 10 Balances to track in a Master Row. - 3 different Transaction History tables, some columns of which can only be determined by client logic (another loooong argument we'll pass on), ie, requiring a parameterized insert by the client. - Each History table has Begin and End Balance columns for the 10 balances. BALANCE_1 needs to be incremented by TRANS_AMT 100, it is currently 500. SELECT BALANCE_1, which returns 500, issue the UPDATE, and of course some guy comes along and decr...

Web resources about - Another SQL Question - sybase.ase.general

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Perth Scorchers loss raises questions on over-reliance on Marsh, Klinger
The reigning BBL champion Perth Scorchers may have a problem on their hands.

Africa: When the Menstrual Cycle Becomes a Question of Human Rights
Opinion - Any views expressed in this article are those of the author and not of Thomson Reuters Foundation.

Questions About the National Guard: Clarification on Title 10 Versus Title 32
Several questions have arisen over the past few days about whether the National Guard could be mobilized by the Oregon governor and sent to Harney ...

iPhone 6s demand called into question as Apple reportedly orders cut back in production
Citing sources familiar with Apple's supply chain, Nikkei is reporting that the company is scaling back iPhone 6s and 6s Plus production by as ...

Donald Trump: Birther questions about Ted Cruz could be a problem
Republican front-runner suggests citizenship questions about Cruz, who was born in Canada, could hurt GOP

Chris Christie Questions If A Woman — Hillary Clinton — Can Drive American Foreign Policy
Chris Christie Questions If A Woman — Hillary Clinton — Can Drive American Foreign Policy

To Keep Resolutions Turn Them Into Questions
Questioning creates a type of pressure to follow through.

Pathankot attack: The answers to five questions everyone asks after a Pak-directed strike on India
As counter-terrorist operations at the Pathankot Indian Air Force (IAF) base continues into the third day, the ordinary man wants answers to ...

12 Questions With Shawn Mendes And His 30 Under 30 Manager Andrew Gertler
Mendes’ break came courtesy of Vine, where he gained millions of followers singing six-second covers of pop songs. Fellow 30 Under 30 2016 honoree ...

Resources last updated: 1/6/2016 1:54:31 PM