Export data using select statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Export data using select statement.

Author

Topic

silvershark
Starting Member

48 Posts

Posted - 2009-01-07 : 15:40:43

I have a select statement that works and I have scripted through vbs to export using the applications export function. However, I was curious if there was a way inside SQL Enterprise Manager to create a job to run the select statement and export the data to the folder of my choosing and then if it completes then run my stored procedure to reset the balance column.

Basically I have a database that keeps A/R charge balances. On a specific date I want to export the data and after it exports I want the next procedure reset the balance information.

Here is what I have so far.

First Job.

select OtdCompany, OtdEmployee, OtdDedCode, OtdDedAmt from AR_CUST where bal > '0' and cust_no not LIKE '883300%' and cust_no not LIKE '*0%' and cust_no not LIKE '00%' and cust_no not LIKE '011' and cust_no not LIKE '10_' and cust_no not LIKE '11_' and cust_no not LIKE '12_' and cust_no not LIKE '13_' and cust_no not LIKE '14_' and cust_no not LIKE '15_' and cust_no not LIKE '16_' and cust_no not LIKE '17_' and cust_no not LIKE '18_' and cust_no not LIKE '19_' and cust_no not LIKE '20_' and cust_no not LIKE '21_' and cust_no not LIKE '22_' and cust_no not LIKE '23_' and cust_no not LIKE '24_' and cust_no not LIKE '25_' and cust_no not LIKE '26_' and cust_no not LIKE '27_' and cust_no not LIKE '28_' and cust_no not LIKE '29_' and cust_no not LIKE 'TEMPLATE'

Second Job: I would select to run only if the first portion completes. Or I could put SQL code in the first Job and not require a second job.

update AR_CUST
set BAL='0'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:20:21

Create a job with multiple steps. Use BCP to export.

E 12°55'05.63"
N 56°04'39.26"

silvershark
Starting Member

48 Posts

Posted - 2009-01-07 : 16:45:49

Ok, I actually just created a vbs script for the second part and it seems to work great. I just made it a sub.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 16:55:16

You can shorten down your statement

select	OtdCompany,
	OtdEmployee,
	OtdDedCode,
	OtdDedAmt
from	AR_CUST
where	bal > '0'
	and cust_no not LIKE '883300%'
	and cust_no not LIKE '*0%'
	and cust_no not LIKE '00%'
	and cust_no not LIKE '011'
	and cust_no not LIKE '10_'
	and cust_no not LIKE '11_'
	and cust_no not LIKE '12_'
	and cust_no not LIKE '13_'
	and cust_no not LIKE '14_'
	and cust_no not LIKE '15_'
	and cust_no not LIKE '16_'
	and cust_no not LIKE '17_'
	and cust_no not LIKE '18_'
	and cust_no not LIKE '19_'
	and cust_no not LIKE '20_'
	and cust_no not LIKE '21_'
	and cust_no not LIKE '22_'
	and cust_no not LIKE '23_'
	and cust_no not LIKE '24_'
	and cust_no not LIKE '25_'
	and cust_no not LIKE '26_'
	and cust_no not LIKE '27_'
	and cust_no not LIKE '28_'
	and cust_no not LIKE '29_'
	and cust_no not LIKE 'TEMPLATE'

to this

select	OtdCompany,
	OtdEmployee,
	OtdDedCode,
	OtdDedAmt
from	AR_CUST
where	bal > '0'
	and cust_no not LIKE '883300%'
	and cust_no not LIKE '*0%'
	and cust_no not LIKE '00%'
	and cust_no not LIKE '011'
	and cust_no not LIKE '[12][0-9]_'
	and cust_no not LIKE 'TEMPLATE'

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources