I have to output into an external file for more than one query out put. Presently what i am doing is :-
bcp "SELECT l.Name, j.Name FROM msdb..sysjobs j INNER JOIN master..syslogins l ON l.sid = j.owner_sid" queryout %5\\JOBS.xls -c -S%1 -U%3 -P %4
bcp "select l.name from master..syslogins l with (nolock) inner join %2..tbluser j on l.name = j.tloginname" queryout %5\\SQLUSERS.xls -c -S%1 -U%3 -P %4
The above are the two queries and i export into two different excel files.
Is there any way i can append the second select query data into the already created excel file?
--Sandui don't think you can do it straight from bcp, but you can add a third line to your batch file:
copy first_file.xls + second_file.xls third_file.xls|||Originally posted by ms_sql_dba
i don't think you can do it straight from bcp, but you can add a third line to your batch file:
copy first_file.xls + second_file.xls third_file.xls
That was cool solution...
Now a few more questions :-)
1. Can i give any spacing between the first and second file data in the third file? Or can i give any heading before the data of the first and the second file?
2. After copying the data into the third file how can i delete the first and the second file through the batch file?
--Sandu_Bangalore|||1. echo. >> third_file.xls
2. del first_file.xls & del second_file.xls
2012年3月19日星期一
2012年2月23日星期四
bcp and order of rows in table
I am loading data from external program using bcp into a temp table.
I assumed that the rows would be loaded sequentially (same order as
file). Is there a option or some other method to load the data in the
same order as the records in the file. Here is what I'm trying to do:
SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
e:\kaleida\smsadtimp.fmt -U bla -P blabla'
EXEC @.R = master.dbo.xp_cmdshell @.C
DECLARE c_adtrecs CURSOR
for
select * from ##tkaladt
process records in the order they are in the file.
A table is, per definition, not ordered. How about adding an identity column, letting SQL Server generate the
identity values as BCP inserts the rows and use that column in your SELECT statement's ORDER BY?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joe R" <jralabate@.kaleidahealth.org> wrote in message news:eQE13hGLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> I am loading data from external program using bcp into a temp table.
> I assumed that the rows would be loaded sequentially (same order as
> file). Is there a option or some other method to load the data in the
> same order as the records in the file. Here is what I'm trying to do:
> SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
> e:\kaleida\smsadtimp.fmt -U bla -P blabla'
>
> EXEC @.R = master.dbo.xp_cmdshell @.C
> DECLARE c_adtrecs CURSOR
> for
> select * from ##tkaladt
> process records in the order they are in the file.
>
I assumed that the rows would be loaded sequentially (same order as
file). Is there a option or some other method to load the data in the
same order as the records in the file. Here is what I'm trying to do:
SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
e:\kaleida\smsadtimp.fmt -U bla -P blabla'
EXEC @.R = master.dbo.xp_cmdshell @.C
DECLARE c_adtrecs CURSOR
for
select * from ##tkaladt
process records in the order they are in the file.
A table is, per definition, not ordered. How about adding an identity column, letting SQL Server generate the
identity values as BCP inserts the rows and use that column in your SELECT statement's ORDER BY?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joe R" <jralabate@.kaleidahealth.org> wrote in message news:eQE13hGLEHA.2660@.TK2MSFTNGP09.phx.gbl...
> I am loading data from external program using bcp into a temp table.
> I assumed that the rows would be loaded sequentially (same order as
> file). Is there a option or some other method to load the data in the
> same order as the records in the file. Here is what I'm trying to do:
> SET @.C='bcp ##tkaladt in ' + @.dir + @.file + ' -f
> e:\kaleida\smsadtimp.fmt -U bla -P blabla'
>
> EXEC @.R = master.dbo.xp_cmdshell @.C
> DECLARE c_adtrecs CURSOR
> for
> select * from ##tkaladt
> process records in the order they are in the file.
>
订阅:
博文 (Atom)