March 22, 2016 at 10:58 am
I have a table A which has some data
guid fbnum date Type
123 234 March 21 II
125 256 march 1 IV
I have another table say B, the data in B is populated from A
ID guid fbnum date type
1 123 234 March 2 III
I have two conditions here
its little complicated
if I want to enter the first record of A into B
it should check for (guid and fbnum) in this case its (123 and 234), these records already exists in the table B
so for these type of records
the insertion should look like this in table B
ID guid fbnum date type
1 123 234 March 2 III
2 123 234 March 2 II
it should replace the date with the most earliest date..
if the records(guid and fbnum) doesn't exists in the table it should do a normal insertion
ID guid fbnum date type
1 123 234 March 2 III
2 123 234 March 2 II
3 125 256 march 1 IV
I am not sure if its clear.
Thanks
March 22, 2016 at 12:07 pm
Couple things you need to clarify.
First, what's the first row? There's no such thing in a SQL table. Until you do an ORDER BY in a query, you can't determine what's first. If it's the date, that's good.
Second, do all changes need to happen at once, or could do this in stages? Meaning if I do some patients, or some inserts separately from others, does that matter? If it doesn't, this is simpler.
Third, please post some DDL and sample data for the tables. That helps with providing a soution.
The first part is a little complicated. What if there is a record that matches with the earliest date? Or if other fields match (as in your example with Type), but some don't. You should consider all the possibilities here with your example.
March 22, 2016 at 12:57 pm
Thanks for you reply
The fields in A table are truncated and populated with new data every week
all other fields might match but the date field doesn't
Not only the first row, it should go through the entire table..
second, all the changes need not happen at once, but can be done in stages also
March 22, 2016 at 1:22 pm
sample data in A
[highlight="#ffff11"]GUID FBNUM CAP DATE prt[/highlight]
123 961149 942770 20160310 0.001
123 961149 942770 20160308 0.001
March 22, 2016 at 2:38 pm
OK, what you really want is setup like this, so someone can test (And you can).
create table a
(
aguid int
, fbnum int
, mydate date
, aType varchar(10)
)
go
create table b
(
id int
, aguid int
, fbnum int
, mydate date
, aType varchar(10)
)
go
insert a
values
(123, 234, 'March 2,2016', 'II')
, (125, 256, 'March 1, 2016', 'IV')
go
insert b
values
(1, 123, 234, 'March 2, 2016', 'III')
go
select * from a;
select * from b;
go
-- get items from a that don't match b
select a.*
from a
inner join b
on a.aguid = b.aguid
and a.fbnum = b.fbnum
go
drop table a
drop table b
Now, if I look at your data, there is a row in 1 that isn't in B, as far as matching guid, fbnum, and type. I'm guessing what you mean is match on those, and if there isn't a match, add it to b.
What you originally listed as "an insertion" looks like the results of b at the end, not an insert. An insertion would be only the new row. An update would be a change to an existing row.
I think you need to look carefully at your initial data. It seems as though you've either described things wrong, or you have a mistake in your data. I'd set up 3-4 cases of things you need to do and then we can write code to handle them. Ideally you'd have a test for each, like this:
exec tsqlt.NewTestClass 'MergeTest';
go
create procedure [MergeTest].[test Update existing fbnum and guid]
as
begin
-- Assemble
exec tsqlt.FakeTable 'a';
insert a
values
(123, 234, 'March 2,2016', 'II')
exec tsqlt.FakeTable 'b';
insert b
values
(1, 123, 234, 'March 1,2016', 'III')
select *
into #Actual
from b
where 1 = 0
insert #Actual
values
(1, 123, 234, 'March 2,2016', 'II')
-- act
update b
set mydate = a.mydate
, mydate = a.mydate
from a
where a.aguid = b.aguid
and a.fbnum = b.fbnum
exec tsqlt.AssertEqualsTable
@expected = '#Actual',
@Actual = 'b';
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply