May 13, 2013 at 12:12 pm
I have a table that holds transactions on specific accounts.
It is populated from another table. That being the case, column tx_id is guaranteed to be unique, so the primary keys are correct.
I now have to add transactions to this table from records from a flat file, and have to
insert rows with my own unique tx_id. I import the flat file into a temp holding table.
The flat file contents will be tx_type = 'CLOS'. This is a new tx_type and does not yet exist in the
transaction table for any accounts. But every account in the file will be in the transaction table.
The flat file can and does contain multiple rows per account.
I can do this with a cursor, but keep failing with a set based solution.
I keep getting primary key violations on tx_id when inserting accounts with multiple rows in the file.
Can anybody tell me what I am doing wrong? Any nudge in the right direction would be appreciated.
Thanks all.
Here's the transaction table:
CREATE TABLE [dbo].[My_Trans_Table](
[tx_date] [datetime] NOT NULL,
[pt_id] [char](13) NOT NULL,
[tx_type] [varchar](4) NOT NULL,
[tx_id] [int] NOT NULL,
[svc_date] [datetime] NOT NULL,
[post_date] [datetime] NULL,
[amt] [decimal](14, 2) NULL,
[ins_ind] [char](1) NOT NULL)
alter table My_Trans_Table
add CONSTRAINT [PK_My_Trans_Table] PRIMARY KEY CLUSTERED
([tx_date] ASC,[pt_id] ASC, [tx_type] ASC,[tx_id] ASC)
and the temp holding table
CREATE TABLE [dbo].[My_Temp_Hold](
[pt_id] [varchar](13) NULL,
[close_date] [varchar](10) NULL,
[close_code] [varchar](10) NULL,
[close_reason] [varchar](30) NULL)
Some example rows:
insert My_Trans_Table
select '2011-12-29 00:00:00.000','000070463567','SENT','0','2011-12-29 00:00:00.000','2011-12-29 00:00:00.000','67.70',''
union all
select '2012-04-27 00:00:00.000','000070463567','ADJ','28','2012-04-26 00:00:00.000','2012-04-26 00:00:00.000','-67.70',''
insert My_Temp_Hold
select '000070463567','20120403','RTNBKRPT','Bankruptcy'
union all
select '000070463567','20120504','RTNREFBD','Refer to BD'
My working curser:
declare @pt_id varchar(13),
@close_date varchar(10)
declare close_curs cursor
for
selectpt_id, close_date
fromMy_Temp_Hold
open close_curs
fetch next from close_curs into @pt_id, @close_date
while (@@fetch_status = 0)
begin
declare @x int
select @x = isnull(max(r.tx_id),0) from My_Trans_Table r where r.pt_id = @pt_id and r.tx_type = 'CLOS'
select @x = @x+1
insertMy_Trans_Table(tx_date, pt_id, tx_type, tx_id, svc_date, post_date, amt, ins_ind)
selectgetdate(),
@pt_id,
'CLOS',
@x,
convert(datetime,@close_date),
getdate(),
0,
''
fetch next from close_curs into @pt_id, @close_date
end
close close_curs
deallocate close_curs
And my latest failed attempt at a set based solution:
insertMy_Trans_Table(tx_date, pt_id, tx_type, tx_id, svc_date, post_date, amt, ins_ind)
selectgetdate(),
k.pt_id,
'CLOS',
(select isnull(max(r.tx_id),0)+1 from My_Trans_Table r where r.pt_id = k.pt_id and r.tx_type = 'CLOS'),
convert(datetime,k.close_date),
getdate(),
0,
''
fromMy_Temp_Hold k
May 13, 2013 at 12:30 pm
You are going to have a nearly impossible time doing this set based because your primary key includes tx_date. I wonder if your cursor actually does what you think it does. The tx_id will be the same for each trip through your cursor.
Change your insert inside your cursor to a select.
open close_curs
fetch next from close_curs into @pt_id, @close_date
while (@@fetch_status = 0)
begin
declare @x int
select @x = isnull(max(r.tx_id),0) from My_Trans_Table r where r.pt_id = @pt_id and r.tx_type = 'CLOS'
select @x = @x+1
--insertMy_Trans_Table(tx_date, pt_id, tx_type, tx_id, svc_date, post_date, amt, ins_ind)
selectgetdate(),
@pt_id,
'CLOS',
@x,
convert(datetime,@close_date),
getdate(),
0,
''
fetch next from close_curs into @pt_id, @close_date
end
close close_curs
deallocate close_curs
Those two rows are identical (except for the datetime column). I don't think that is really what you are after??? The reason the insert works there and not in your query is because in your query the timestamp will be identical for each which causes a primary key violation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2013 at 1:00 pm
The cursor works. The max(tx_id)+1 does not get incremented when you comment out the insert and just display the results.
So I'm not crazy or lazy using a cursor in this case? Good.
This is not a high volume task. A couple thousand rows once a week tops. A cursor won't hurt anything here except my pride.
I just couldn't get a set based result to work and thought I should be able to. So I wrote the cursor to get the rows in. I wanted to see if it made any difference when a 'CLOS' transaction was already present for an account.
May 13, 2013 at 1:36 pm
Randy Doub (5/13/2013)
The cursor works. The max(tx_id)+1 does not get incremented when you comment out the insert and just display the results.So I'm not crazy or lazy using a cursor in this case? Good.
This is not a high volume task. A couple thousand rows once a week tops. A cursor won't hurt anything here except my pride.
I just couldn't get a set based result to work and thought I should be able to. So I wrote the cursor to get the rows in. I wanted to see if it made any difference when a 'CLOS' transaction was already present for an account.
I was thinking that the tx_id being the same was an issue but apparently that is what you want. 🙂
I think that the reason you need a cursor for this is an indication that the ddl is not as well formed as it could be. Once you have the datetime of the record as part of a composite key that should be a sign that what you are using for a key is not right. If anything just add an identity column. That is a random value just like your datetime. You would however gain the advantage of being able to do this type of thing in a single pass. The affect of that is that you have a VERY weak primary key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2013 at 2:07 pm
tx_id is not the same when the curser is run. Each record get's its own sequential tx_id for a CLOS transaction type:
tx_date pt_id tx_type tx_id svc_date
------------------------------------------------------ ------------- ------- ----------- ------------------------------------------------------
2011-12-29 00:00:00.000 000070463567 SENT 0 2011-12-29 00:00:00.000
2012-04-27 00:00:00.000 000070463567 ADJ 28 2012-04-26 00:00:00.000
2013-05-13 14:44:54.293 000070463567 CLOS 1 2012-04-03 00:00:00.000
2013-05-13 14:44:54.293 000070463567 CLOS 2 2012-05-04 00:00:00.000
Yes, I agree it's not ideal. The My_Trans_Table's primary keys are in place I believe to prevent the process that inserts rows in it from running more than once a day. I am using this same table to now store new transactions from a different source. So it's not a good fit, but fit it must.
Thanks for all your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply