March 30, 2011 at 10:13 am
I have a single column table that I am inserting data into nightly. Once this data is in the table, I am going to select out of it into a text file (to then be imported into another system). The first record that I want to insert is a header record, which is a two digit month, day, and year line. All of the other records are lines of data, formatted in specific lenghts (when the text file is imported into the other system, it needs to be fixed lenght fields).
The issue is this. Even though I am inserting the header record before I start to insert any of the other records, occasionally the header record is ending up inserted farther down in the table (as record 400, record 510, etc..)
Below is my logic:
-- first thing is to clear the table out before repopulating overnight
DELETE from dw_table where data is not null
-- insert header record
INSERT INTO dw_table(data)
VALUES (@curr_month + @curr_day + @curr_year)
DECLARE c_pto CURSOR
FOR
SELECT transactionid, transdate, userid, transcode, officecode, cardnumber,
number, billingid, description, typeoffiling, amount, voiddesc, exportdate,
tkloc, trans_month, trans_day, trans_year
FROM dw_trans
OPEN c_pto
FETCH NEXT FROM c_pto INTO @r_transactionid, @r_transdate, @r_userid, @r_transcode, @r_officecode,
@r_cardnumber, @r_number, @r_billingid, @r_description, @r_typeoffiling, @r_amount,
@r_voiddesc, @r_exportdate, @r_tkloc, @r_trans_month, @r_trans_day, @r_trans_year
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert the first line for each record
INSERT INTO dw_table(data)
VALUES(@t_rectype + @r_trans_month + @r_trans_day + @r_trans_year + rtrim(@r_number) + ' ' + left(rtrim(@r_billingid)+' ',5) + @t_cost_code + @t_quantity + @t_rate + right(' '+rtrim(@r_amount),11) + @r_tkloc)
FETCH NEXT FROM c_pto INTO @r_transactionid, @r_transdate, @r_userid, @r_transcode, @r_officecode,
@r_cardnumber, @r_number, @r_billingid, @r_description, @r_typeoffiling, @r_amount,
@r_voiddesc, @r_exportdate, @r_tkloc, @r_trans_month, @r_trans_day, @r_trans_year
END
CLOSE c_pto
DEALLOCATE c_pto
There are other insert statements being done within the cursor, but they are the same as the one above. Once all lines have been inserted into the table, I am doing a select into the text file:
set @cmd1 = 'bcp "select * from dw_table" queryout "\\testdir\PT'+@curr_year+@curr_month+@curr_day+'.txt" -T -c -t,'
EXEC xp_cmdshell @cmd1
It doesn't make sense to me why sometimes the header record is first, and sometimes it's ending up farther down in the table. I thought of adding an auto-incrementing id column to the table and then ordering by that, but it wouldn't be solved because it could still be inserting the header record not in the first row.
Wasn't sure if it is something with the delete at the beginning, or possibly something with the cursor? I've never seen this occur and since it's not happening every night, it's becoming very difficult to pinpoint. In the past week it worked last Thursday, broke on Friday, worked on Monday, broke on Tuesday, and worked today.....which does seem to indicate a pattern of every other day.
Thanks so much in advance for any thoughts on this issue.
March 30, 2011 at 10:17 am
tables do not have an order, so the order you insert the data does not really matter. you may luck out every now and again and retain the order but it is not guaranteed.
The only way to guarantee the order of a query is to use the ORDER BY clause.
so maybe you need to create a column that defines if the record is a header and then order by this..
March 30, 2011 at 2:24 pm
steveb,
thanks for the thought; I'll add a column (auto incrementing), order by that, in which case that should solve my problem.
Thanks!
March 30, 2011 at 5:59 pm
First my assumptions.
1. Your table dw_trans was not defined in your posting
2...Using a guesstimate approach I created a table dw_trans
3. Using my guestimated data I loaded a few rows into the dw_trans table
4. Using CURSORS is most inefficient, so I elected to use a SET based
approach which both simplifies the total code required and runs a heck of a lot faster
5. I assumed that your output would NOT be fixed length fields, I elected to use a pipe character "|" as a field delimiter.
6, You can then use your BCP out from the #T table.
7. My final SELECT statement has an ORDER BY clause - to illustrate
how you can decide the record sequence. Note that the initial header row has the seq number of 1, and each row of additional data row has an associated unique seq number.
That said .. here we go.
CREATE TABLE #T(Seq INT IDENTITY(1,1),xdata VARCHAR(1000))
SET DATEFORMAT mdy;
INSERT INTO #T(xdata)
VALUES(CAST(DATEPART (mm , GETDATE()) AS VARCHAR(2))+ '|' +
CAST(DATEPART (dd , GETDATE()) AS VARCHAR(2)) + '|' +
CAST(DATEPART (yy , GETDATE()) AS VARCHAR(4)))
GO
-- select * FROM #T for test purposes only
CREATE TABLE #dw_trans(transactionid VARCHAR(10), transdate DATETIME, userid INT, transcode VARCHAR(20))
INSERT INTO #dw_trans
SELECT '123',GETDATE(),1,'456789' UNION ALL
SELECT '000',GETDATE(),99,'98765' UNION ALL
SELECT '555',GETDATE(),1000,'0909098-760'
-- SELECT * FROM #dw_trans for testing only
INSERT INTO #T
SELECT transactionid + '|' + CAST(transdate AS VARCHAR(20)) +'|' + CAST(userid AS VARCHAR(10))
+ '|' + transcode
FROM #dw_trans
-- SELECT * FROM #T for testing
SELECT Seq, xdata FROM #T ORDER BY Seq DESC
Results:
Seq xdata
4555|Mar 30 2011 7:41PM|1000|0909098-760
3000|Mar 30 2011 7:41PM|99|98765
2123|Mar 30 2011 7:41PM|1|456789
13|30|2011
Hope all these suggestions gets your creative juices flowing and you can devise a rapid, bullet proof solution.
April 15, 2011 at 9:09 am
Ron and Steve,
Thanks for all of your help. Things have been running perfectly for over two weeks now, when yesterday an issue popped up. The issue occured with the bcp command, which is as follows:
(data is the field that holds my fixed length fields, counter is a auto-incrementing column to keep the proper order of the fields)
set @cmd1 = 'bcp "select pdata from dw_precost order by counter" queryout "..@curr_year+@curr_month+@curr_day+'.txt" -T -c -t,'
EXEC xp_cmdshell @cmd1
2 of the pdata fields were split when being inserted into the text file (example_)
how the fields are in the table:
4this is how I expect the fields to be situated
4 and this is another field
but in the text file, they were inserted as so:
4this is how I e
pect the fi
elds to be situated
4 and thi
s is another field
This is my first time using the bcp commands, so is there something I'm missing that would potentially split a field up? It's strange because this is the only instance of this occuring. Any thoughts?
Thanks!
jamey8420
April 27, 2011 at 1:32 pm
I figured out what the issue was. The place that we were getting the data from (another system), users were entering in carriage return/line feed combinations while they entered in text for the descriptions. While these CR-LF looked like a space in the table, the codes were still there and when the text file was created, they were interpreted. This caused my lines to split when I didn't want them to. I am replacing the CRLF with spaces in my descriptions; this is no longer an issue.
thanks,
jamey8420
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply