June 2, 2002 at 3:40 pm
I want to loop through a table holding transactions for 2 years and I want to extract them in a monthly order (e. g. 2002_05.txt etc). How I can loop through (with a cursor?) and extract data without going through a second time (check if file exists and start at the new position - data not yet transferrred?) and if successful delete the transaction in the relevant table? See code example below.
Any hints available?
Thanks Mipo
Drop table ##temp
Declare @Year varchar (30)
Declare @Month varchar (30)
Declare @cmd varchar(255)
Declare @temp integer
Select @Year = '2002'
Select @Month = '5'
select * into ##temp
from transactions
where DatePart (year, TransactionDateStamp)= @year
and DatePart (month, TransactionDateStamp) = @month
select @cmd = 'bcp "select * from ##temp" queryout D:\MSSQL2000\BackUpFlatFiles\' + @year + '_' + @month + '.txt -c -C1250 -Swin2000 -Uopms -Psmpo'
print @cmd
exec master.dbo.xp_cmdshell @cmd
June 3, 2002 at 5:45 am
Perhaps look at an alternative solution. Extract the list of year.month pairs in your data. Then loop thru each record in the result set, passing the date range as parameters to the BCP command.
Anyhow here is a likely script. This does not use cursors, but instead the TOP 1 clause of the select statement to determine the next record to use. Amend to include your deleting of records.
/* start of script */
Declare @Year varchar (30)
Declare @Month varchar (30)
Declare @cmd varchar(255)
Declare @temp integer
declare @continue integer
declare @trxYearMonthStart datetime
declare @trxYearMonthEnd datetime
/* temp table for range of months in database */
drop table ##tempYYMM
create table ##tempYYMM
(
trxYearMonthStart datetime,
trxYearMonthEnd datetime,
nmbrtrx decimal(12,0),
processed int
)
/* get the year.month values */
insert ##tempYYMM
(
trxYearMonthStart,
nmbrtrx
)
select
convert(varchar(6), TransactionDateStamp , 112)+'01',
count(*)
from transaction
group by
convert(varchar(6), TransactionDateStamp , 112)+'01'
/* get the end of the month */
update ##tempYYMM
set
trxYearMonthEnd = dateadd(dd,-1, dateadd(mm,1,trxYearMonthStart)),
processed = 0
-- select * from ##tempYYMM
/* loop to process */
select @continue = 1
while @continue = 1 begin
/* get the next year/mmonth pair */
select top 1
@trxYearMonthStart = trxYearMonthStart,
@trxYearMonthEnd = trxYearMonthEnd
from ##tempYYMM
where processed = 0
select @continue = @@ROWCOUNT
if @continue = 0 begin
break
end
/* mark this year/month as processed */
update ##tempYYMM set processed = 1
where trxYearMonthStart = @trxYearMonthStart
/* put together the bcp select string */
Select @Year = convert(varchar(4), @trxYearMonthStart, 120)
Select @Month = right(convert(varchar(7), @trxYearMonthStart, 120),2)
select @cmd = 'bcp "select * from transaction '
select @cmd = @cmd + 'where TransactionDateStamp between '
select @cmd = @cmd + '''' + convert(varchar(10),@trxYearMonthStart,120) + ''' and ''' + convert(varchar(10),@trxYearMonthEnd,120) + ''''
select @cmd = @cmd + ' " queryout D:\MSSQL2000\BackUpFlatFiles\' + @Year + '_' + @Month + '.txt '
select @cmd = @cmd + ' -c -C1250 -Swin2000 -Uopms -Psmpo'
print @cmd
exec master.dbo.xp_cmdshell @cmd
end
/* end of script */
Trust this helps
Robert
Edited by - rcavill on 06/03/2002 05:55:12 AM
June 5, 2002 at 3:18 pm
Thanks a lot for your input - it works well!
By implementing it the question was raised what happens if bcp fails and data is not extracted and deletion of data of relevant table has been started although not all data has been extracted. Is there a checking possible if all flat files are there onto harddisk and if so start deleting data older than a year for example.
In addition how can I add a task deleting these flat files older than 2 years from harddisk? Probably with DTS?
Thanks again for your input
Mipo
June 6, 2002 at 12:37 am
Yes, you can check for the successful operation of bcp.
There are multiple ways. However I will show you one contained purely in t-sql.
This requires you to capture the results returned from the exec command
Scan the table for expected success or failure of bcp and perform an action
on the success or failure.
So
exec @master.dbo.xp_cmdshell @cmd
becomes
delete #cmdlines
insert #cmdlines
exec master.dbo.xp_cmdshell @cmd
if exists (select 1 from #cmdlines where outputstr like '%rows copied%') begin
-- delete records as successfully copied
end
else begin
-- log failure
end
You will also need to add the following at the top of the script
-- at top of script
create table #cmdlines (outputstr varchar(1000), lineid integer identity(1,1))
The same principle of capturing the output from the exec xp_cmdshell can be done to get a directory listing. Then split the #cmdline table into expected fields based on a fixed length field basis.
insert #cmdlines
exec master.dbo.xp_cmdshell 'dir D:\MSSQL2000\BackUpFlatFiles\*.txt'
Though I am sure you can find something more suited in DTS
June 12, 2002 at 4:09 pm
Thanks again for your info.
I have another question - how can I pass to the construct above different table names and process them as described above. The table names will be read previously from an ini file (from DTS task) and these table names will be passed as variables to a stored procedure containing the construct above. How can I do that?
Thanks for any input
mipo
June 13, 2002 at 9:02 am
Only two changes are necessary to the above code to allow it to be converted a stored procedure that accepts the table name to process. assumes the parameter name is @selectedtable
1. gettting months in the target table.
change from:
/* get the year.month values */
insert ##tempYYMM
(
trxYearMonthStart,
nmbrtrx
)
select
convert(varchar(6), TransactionDateStamp , 112)+'01',
count(*)
from transaction
group by
convert(varchar(6), TransactionDateStamp , 112)+'01'
to
/* get the year.month values */
exec ('insert ##tempYYMM
(
trxYearMonthStart,
nmbrtrx
)
select
convert(varchar(6), TransactionDateStamp , 112)+''01'',
count(*)
from ' + @selectedtable + ' group by
convert(varchar(6), TransactionDateStamp , 112)+''01''
' )
2. bcp command
change from:
select @cmd = 'bcp "select * from transaction '
to:
select @cmd = 'bcp "select * from ' + @selectedtable + ' '
3. Deleting records
You will similarly have to wrap your delete statement upon successful bcp within an exec() so as to pass in the table name.
Robert
June 23, 2002 at 2:51 pm
Hi, thanks again for your information.
How can I overcome the problem if you do not know the column name where the datepart resides in the selected table. For example I have two tables both containing date and time stamps but the column name is different one time it is called transactiondatestamp and in the other table EntryDate.
Is there a workaround possible?
thanks mipo
June 23, 2002 at 7:46 pm
Yes, there are possible workarounds.
#1. You are already passing in the table name. Given the table name you will know which field contains the date. Use the If-Else clause and have two copies of the code, but specific to each table. This approach is easier to debug when limited number of tables (<6).
#2. Have another parameter that passes in the field name containing the datefield.
You will then have to amend the code to use the parameter. Specifically, amend the code as follows from:
* get the year.month values */
exec ('insert ##tempYYMM
(
trxYearMonthStart,
nmbrtrx
)
select
convert(varchar(6), TransactionDateStamp , 112)+''01'',
count(*)
from ' + @selectedtable + ' group by
convert(varchar(6), TransactionDateStamp , 112)+''01''
' )
to:
/* get the year.month values */
exec ('insert ##tempYYMM
(
trxYearMonthStart,
nmbrtrx
)
select
convert(varchar(6), ' + quotename(@selectedfield) + ' , 112)+''01'',
count(*)
from ' + @selectedtable + ' group by
convert(varchar(6), ' + quotename(@selectedfield) + ', 112)+''01''
' )
2. bcp command
change from:
select @cmd = 'bcp "select * from transaction '
select @cmd = @cmd + 'where TransactionDateStamp between '
to:
select @cmd = 'bcp "select * from ' + @selectedtable + ' '
select @cmd = @cmd + 'where ' + quotename(@selectedfield) + ' between '
You should be able to see a pattern developing here. Everytime you need to refer to a column or table name that can change, use a variable and then embed the actual command construct within EXEC(). There are plenty of articles and scripts on this site that use the technique.
June 24, 2002 at 2:02 am
Thanks for the superb workaround.
A last question - how would you pass on a varialbe (containing a table name or more) to this procedure and process it? With other words
the table name will be read out of an ini file e. g. SelectedTable=Transactions, Statistics, Payments
passed on to the procedure and processed in the bcp procedure sequentially (3 tables in this example).
Do I have to split up the string containing all three tables and process each table after the other?
Thanks for any input
mipo
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply