T-SQL to create text file ?

[Ben]

We've a series of archived tables generated dynamically, eg. TAB_X199911,
TAB_X199912, TAB_X200001 etc. After a while, we want to dump those older
tables to the respective text files. eg. TAB_X199911 -> X199911.txt, TAB_X199912
-> X199912.txt etc.

Can T-SQL do this? If not, pls advise on the right way?

(Have tried bcp command, but seems only able to provide fixed name.)

Thanks in advance,
Ben

[Andrew Prosser]

"Ben" <bluebells88@yahoo.com> wrote:
>
>We've a series of archived tables generated dynamically, eg. TAB_X199911,
>TAB_X199912, TAB_X200001 etc. After a while, we want to dump those older
>tables to the respective text files. eg. TAB_X199911 -> X199911.txt, TAB_X199912
>-> X199912.txt etc.
>
>Can T-SQL do this? If not, pls advise on the right way?
>
>(Have tried bcp command, but seems only able to provide fixed name.)
>
>Thanks in advance,
>Ben
>
>

BCP can do this, ie;

The bcp utility allows you to copy the result set from a Transact-SQL statement
to a data file. The Transact-SQL statement can be any valid statement that
returns a results set, such as a distributed query or a SELECT statement
joining several tables. For example, to copy the names of all the authors,
ordered by surname, from the authors table in the pubs database to the Authors.txt
data file, execute at the command prompt:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout
Authors.txt -c -Sservername -Usa -Ppassword

[Ben]

"Andrew Prosser" <andrew_prosser@bigfoot.com> wrote:
>
>"Ben" <bluebells88@yahoo.com> wrote:
>>
>>We've a series of archived tables generated dynamically, eg. TAB_X199911,
>>TAB_X199912, TAB_X200001 etc. After a while, we want to dump those older
>>tables to the respective text files. eg. TAB_X199911 -> X199911.txt, TAB_X199912
>>-> X199912.txt etc.
>>
>>Can T-SQL do this? If not, pls advise on the right way?
>>
>>(Have tried bcp command, but seems only able to provide fixed name.)
>>
>>Thanks in advance,
>>Ben
>>
>>
>
>BCP can do this, ie;
>
>The bcp utility allows you to copy the result set from a Transact-SQL statement
>to a data file. The Transact-SQL statement can be any valid statement that
>returns a results set, such as a distributed query or a SELECT statement
>joining several tables. For example, to copy the names of all the authors,
>ordered by surname, from the authors table in the pubs database to the Authors.txt
>data file, execute at the command prompt:
>
>bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout
>Authors.txt -c -Sservername -Usa -Ppassword
>
>

Can the filename in the bcp be a variable? I was thinking of having a job
to scan thru all the archived tables. Say if TAB_X199911 is 'old enough',
then bcp to a filename which is dependent on the tablename. eg. TAB_X199911
to X199911.txt

Because the no. of archived tables grow, (this month TAB_X200001, next month
new table TAB_X200002) therefore couldn't defined all the tables in advance
in the job.

Thanks,
Ben

[James T. Stanley]

Of course the file name can be a variable. Just create the bcp command in a
varchar and execute it using xp_cmdshell.

"Ben" <bluebells88@yahoo.com> wrote in message
news:3a8b3878$1@news.devx.com...
>
> "Andrew Prosser" <andrew_prosser@bigfoot.com> wrote:
> >
> >"Ben" <bluebells88@yahoo.com> wrote:
> >>
> >>We've a series of archived tables generated dynamically, eg.
TAB_X199911,
> >>TAB_X199912, TAB_X200001 etc. After a while, we want to dump those
older
> >>tables to the respective text files. eg. TAB_X199911 -> X199911.txt,
TAB_X199912
> >>-> X199912.txt etc.
> >>
> >>Can T-SQL do this? If not, pls advise on the right way?
> >>
> >>(Have tried bcp command, but seems only able to provide fixed name.)
> >>
> >>Thanks in advance,
> >>Ben
> >>
> >>
> >
> >BCP can do this, ie;
> >
> >The bcp utility allows you to copy the result set from a Transact-SQL
statement
> >to a data file. The Transact-SQL statement can be any valid statement
that
> >returns a results set, such as a distributed query or a SELECT statement
> >joining several tables. For example, to copy the names of all the
authors,
> >ordered by surname, from the authors table in the pubs database to the
Authors.txt
> >data file, execute at the command prompt:
> >
> >bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout
> >Authors.txt -c -Sservername -Usa -Ppassword
> >
> >
>
> Can the filename in the bcp be a variable? I was thinking of having a job
> to scan thru all the archived tables. Say if TAB_X199911 is 'old enough',
> then bcp to a filename which is dependent on the tablename. eg.
TAB_X199911
> to X199911.txt
>
> Because the no. of archived tables grow, (this month TAB_X200001, next
month
> new table TAB_X200002) therefore couldn't defined all the tables in
advance
> in the job.
>
> Thanks,
> Ben

[muringayi, blessed kudzai]

You have the beginnings of the solution. It appears that the text files
that
you are generating using bcp actually follow the format yyyymm.
Therefore
the solution for you is to create a batch file that will name the files
for
you. Please feel free to use the enclosed one and modify it for your
purpose


@echo off

rem the for command takes as its input the output of date /t
rem it separates each line of input into tokens using the forward
rem slash and space as delimiters andit assigns tokens to 1-4 variables

for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set Month=%%j
set Day=%%k
set Year=%%l
)

set PREFIX=%Day%%Month%%Year%
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout x%PREFIX%.txt -c -Sservername -Usa -Ppassword


The above method should actually allow you to automate your process.

BK

BK (Blessed Kudzai Muringayi)
DBA
Phone: 0141 565 6528



"Ben" <bluebells88@yahoo.com> wrote:
>
>"Andrew Prosser" <andrew_prosser@bigfoot.com> wrote:
>>
>>"Ben" <bluebells88@yahoo.com> wrote:
>>>
>>>We've a series of archived tables generated dynamically, eg. TAB_X199911,
>>>TAB_X199912, TAB_X200001 etc. After a while, we want to dump those older
>>>tables to the respective text files. eg. TAB_X199911 -> X199911.txt, TAB_X199912
>>>-> X199912.txt etc.
>>>
>>>Can T-SQL do this? If not, pls advise on the right way?
>>>
>>>(Have tried bcp command, but seems only able to provide fixed name.)
>>>
>>>Thanks in advance,
>>>Ben
>>>
>>>
>>
>>BCP can do this, ie;
>>
>>The bcp utility allows you to copy the result set from a Transact-SQL statement
>>to a data file. The Transact-SQL statement can be any valid statement that
>>returns a results set, such as a distributed query or a SELECT statement
>>joining several tables. For example, to copy the names of all the authors,
>>ordered by surname, from the authors table in the pubs database to the
Authors.txt
>>data file, execute at the command prompt:
>>
>>bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout
>>Authors.txt -c -Sservername -Usa -Ppassword
>>
>>
>
>Can the filename in the bcp be a variable? I was thinking of having a job
>to scan thru all the archived tables. Say if TAB_X199911 is 'old enough',
>then bcp to a filename which is dependent on the tablename. eg. TAB_X199911
>to X199911.txt
>
>Because the no. of archived tables grow, (this month TAB_X200001, next month
>new table TAB_X200002) therefore couldn't defined all the tables in advance
>in the job.
>
>Thanks,
>Ben