A UNION made in heaven, or hades?

Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.

However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.

I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:

"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"

Perhaps I am getting too fancy, but thought I could do it in a single select.

here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9

INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9

select sym, rank
from ( select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym

DROP TABLE tMyPicks
DROP TABLE tUrPicks

This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.

IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.

Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.

I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(I'd suggest:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
ORDER BY 1
-PatP|||How about a slightly different tack...

select you.sym, you.rank , me.sym, me.rank
from tMyPicks me full outer join tUrPicks you on me.sym = you.sym
where (you.sym is null or me.sym is null)
or you.rank <> me.rank
order by you.sym

Sniped for using longer table synonyms ;-)|||Sniped for using longer table synonyms ;-)It's a rough neighborhood, what can I say?

-PatP|||YESSSSS!!!

You guys RULE!!! I KNEW I was making it too hard...and there you are, Pat...allowing me an opportunity to use my favorite function, COALESCE!!! *LOL*

Thanks much guys...I sincerely (as always) appreciate your time!!!|||Dang...so close...Actually, I left out an important aspect of my problem...one that throws a monkey wrench into the whole deal.

Alas, There can also be situations (typically at the lower end of the ranked list) in which the symbol is NOT in BOTH of the two tables. That's where my UNION was helpful...

That complicates the join, I know...if anyone has a quick adjustment, that would be much appreciated, otherwise I'll keep playing around with things. I adjusted the original post to show the data as it could be out there...

I did notice that my test data in my code above did not address this situation either...thank goodness for the ability to test against the live data ;)|||symbol not in both tables is covered by FULL OUTER JOIN

MCrowley's WHERE clause handles it well|||Then I'd use what is basically MCrowley's solution, something like:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1-PatP|||Wow, and I still get to use COALESCE *LOL*

Seriously, thanks again for your help. It seems so plain and obvious once someone else writes it down (oh, and that "figures it out" part too. ;) )

Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know.|||"Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know."

Be aware that such a situation would result in your immediate and permanent banishment from the forum. :)|||It's a rough neighborhood, what can I say?

And I would not have it any other way.

The thought occurs to me, though, that the data should probably all be in a single table of picks. Then it would be easier to get a third, fourth, or fifth set of picks in the database. The query would change a little bit to something like:

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM (select * from picks where picker = 'me') AS m
FULL OUTER JOIN (select * from picks where picker = 'you') AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1|||Well, I would also take that tack if'n it was up to me, and it fit with the plan ;) However, what y'all did not know (because, as you know, knowledge is power, and I only let on what I dared to let on...) is that I am just writing a stored proc that I can use to verify daily processing results in two different, but identical databases on different servers.

We have a production system that uses the Poor Man's Redundancy scheme...that is, two servers that, under the best of situations, contain databases that are *coff, coff* MIRRORS of each other, but are completely independent. Both start off with the same data in "identical" databases, and theoretically, after importing the "same" data from the "same" source each day, process independently (using the "same" stored procedures) and should, theoretically, arrive at the end of the processing day with exactly the same data in the respective databases.

That said, all the quotes should tell ya that it doesn't always happen that way. Sometimes an FTP or fails, and imported data isn't "identically" imported to each half of the mirrored system. What I am doing is trying to write a quick and dirty proc that checks one part of the end-result on each server, then compares the stock rankings to make sure both systems arrived at the same results at the end of the day.

If nothing else, this allows me to jump on the problem BEFORE the data gets out to the end users, and resolve any system/data issues that cause a disagreement between the two servers.

*phew* That said, I also neglected to show in my original code posting that there is also a DATE aspect of the select, so I had to play around with the code you kind gentlement provided yesterday in order to take that into account.

Here, for the sake of posterity (or is that posteriority?), is what I ended up with:CREATE TABLE tMyPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

CREATE TABLE tUrPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

INSERT INTO tMyPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101), 'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'HERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XHERE', 9

INSERT INTO tUrPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101),'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'NHERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XNHERE', 9

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON ((u.myDate = m.myDate) AND (u.sym = m.sym))
WHERE (COALESCE(U.mydate, M.myDate) = '2005-01-24') AND
((m.rank <> u.rank) OR (m.sym IS NULL) OR (u.sym IS NULL))
ORDER BY 1

DROP TABLE tMyPicks
DROP TABLE tUrPicks

this results in output that I need, which is:

XBFD 4 5
XHERE 9 NULL
XIMA 5 7
XNHERE NULL 9
XSOB 7 8
XSORU 8 4

As always, thanks for your help!

A problem with DTS Import/Export wizard....

Hello,
I am trying to replace data in the "region" column in table 2 with data in
the "region" column in table 1...in other words 1 is the source and 2 is the
destination. I go thru the wizard and then use a query to specify the data
to transfer. I go into the query builder and select the column that I want
transfered. I don't specify a sort order or criteria. The query statement
is thus:
select [Customers].[Region]
from [Customers]
easy enough...
I then select the source table and click transform and I do not see an
option to replace the data only append, create destination table, and delete
rows in destination table.
Is this all the functionality of the wizard gives me?
Do I need to create my own script?
Thanks
Ken S.I am sure that someone can help, but we will need some additional =information...
Do the tables (table1 and table2) share a common column that you can =JOIN on in order to perform an update?
What other data exists within Table2? Does all the data come from =Table1? Can you simply delete all the rows within Table2 and insert =from Table1?
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> > I am trying to replace data in the "region" column in table 2 with =data in
> the "region" column in table 1...in other words 1 is the source and 2 =is the
> destination. I go thru the wizard and then use a query to specify the =data
> to transfer. I go into the query builder and select the column that I =want
> transfered. I don't specify a sort order or criteria. The query =statement
> is thus:
> > select [Customers].[Region]
> from [Customers]
> > easy enough...
> > I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and =delete
> rows in destination table.
> > Is this all the functionality of the wizard gives me?
> > Do I need to create my own script?
> > Thanks
> > Ken S.
> >|||Thanks Keith...
both tables are identical tables with the same structure and row count. The
region column in table 1 is the data I want in table 2's region column.
Perhaps your solution of deleting all the rows in table 2 and replacing with
table 1 rows is the way to go.
Thanks
Ken
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
I am sure that someone can help, but we will need some additional
information...
Do the tables (table1 and table2) share a common column that you can JOIN on
in order to perform an update?
What other data exists within Table2? Does all the data come from Table1?
Can you simply delete all the rows within Table2 and insert from Table1?
--
Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am trying to replace data in the "region" column in table 2 with data in
> the "region" column in table 1...in other words 1 is the source and 2 is
the
> destination. I go thru the wizard and then use a query to specify the
data
> to transfer. I go into the query builder and select the column that I
want
> transfered. I don't specify a sort order or criteria. The query
statement
> is thus:
> select [Customers].[Region]
> from [Customers]
> easy enough...
> I then select the source table and click transform and I do not see an
> option to replace the data only append, create destination table, and
delete
> rows in destination table.
> Is this all the functionality of the wizard gives me?
> Do I need to create my own script?
> Thanks
> Ken S.
>|||Since the tables are identical you could also update the data with an =update statement:
UPDATE table2 SET region =3D B.region
FROM table2 A JOIN table1 B ON A.ThePrimaryKeyColumn =3D =B.ThePrimaryKeyColumn
SELECT @.@.rowcount
-- Keith
"SMAN" <ksanti@.nycap.rr.com> wrote in message =news:uKByO2jxDHA.3116@.tk2msftngp13.phx.gbl...
> Thanks Keith...
> > both tables are identical tables with the same structure and row =count. The
> region column in table 1 is the data I want in table 2's region =column.
> Perhaps your solution of deleting all the rows in table 2 and =replacing with
> table 1 rows is the way to go.
> > Thanks
> > Ken
> > > "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:ejEZiujxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I am sure that someone can help, but we will need some additional
> information...
> > Do the tables (table1 and table2) share a common column that you can =JOIN on
> in order to perform an update?
> > What other data exists within Table2? Does all the data come from =Table1?
> Can you simply delete all the rows within Table2 and insert from =Table1?
> > --
> Keith
> > > "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eJUmwnjxDHA.2456@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I am trying to replace data in the "region" column in table 2 with =data in
> > the "region" column in table 1...in other words 1 is the source and =2 is
> the
> > destination. I go thru the wizard and then use a query to specify =the
> data
> > to transfer. I go into the query builder and select the column that =I
> want
> > transfered. I don't specify a sort order or criteria. The query
> statement
> > is thus:
> >
> > select [Customers].[Region]
> > from [Customers]
> >
> > easy enough...
> >
> > I then select the source table and click transform and I do not see =an
> > option to replace the data only append, create destination table, =and
> delete
> > rows in destination table.
> >
> > Is this all the functionality of the wizard gives me?
> >
> > Do I need to create my own script?
> >
> > Thanks
> >
> > Ken S.
> >
> >
> >

A problem in memory when I run sp with a large number of records !

I will describe my problem and I hope that you will help me to find the way to solve it !

My task is how to find the "similiar" words in our database ! Now, I have to use my software to find the similiar name. For example :

The name : "Donaldson Filtration Slovensko s.r." and the name : "PENTA Slovensko Donaldsin spol. sro" are "similiar". In that case : the word "Donaldson" and "Donaldsin" can be called similiar because they are diffrence not more one character in the same positon in the string.

I used stored procedure to sovle this problem because it is a larg number of records in each table (for over 1 million records for each table).

The way I had used first is create a temperary table to contain

CREATE TABLE #SeperateString1
(
String1 nvarchar(100)
)

CREATE TABLE #SeperateString2
(
String2 nvarchar(100)
)

So that, I will analyze those string into 2 tables by detecting the blank character between 2 words !

Then, I compare values between 2 tables,

_

DECLARE ST1_Cursor CURSOR FOR
SELECT String1 FROM #SeperateString1
OPEN ST1_Cursor

FETCH NEXT FROM ST1_Cursor INTO @.String1
WHILE @.@.FETCH_STATUS = 0
Begin

DECLARE ST2_Cursor CURSOR FOR
SELECT String2 FROM #SeperateString2
OPEN ST2_Cursor

FETCH NEXT FROM ST2_Cursor INTO @.String2
WHILE @.@.FETCH_STATUS = 0
Begin

Exec CompareString @.String1, @.String2, @.Result = @.KetQua Output

If @.KetQua = 1
Begin
Exec SetCol1 @.ID, @.Rescol1 = @.Rescol1 Output
Exec SetCol2 @.IDCI, @.Rescol2 = @.Rescol2 Output
Select @.ResCol3 = 'Name: '+ Char(13) + Char(10) + @.String1 + ' <==> ' + @.String2
Select @.ResCol4 = 0

Select @.ResCol5 = GetDate()

If (Not Exists(Select * From Result Where IAdata like @.ResCol1 And DataFound like @.ResCol2 And Reason like @.Rescol3)) And (Not Exists(Select * From Dictionary Where Value = @.String1)) And (Not Exists(Select * From Dictionary Where Value = @.String2))
Begin
INSERT INTO Result (IAData, DataFound, Reason, Decide, DateNow) VALUES (@.ResCol1, @.ResCol2, @.ResCol3, @.ResCol4, @.ResCol5)
End

End
FETCH NEXT FROM ST2_Cursor INTO @.String2
End

CLOSE ST2_Cursor
DEALLOCATE ST2_Cursor

FETCH NEXT FROM ST1_Cursor INTO @.String1
End

CLOSE ST1_Cursor
DEALLOCATE ST1_Cursor

__
Results will be add to "Result" table

After that, I will deallocate my temporary tables which I have created.

_
drop table #SeperateString1
drop table #SeperateString2
_

This SP will be run in about 3 hours !
Problems occour is memory ! For about an hour to run, my memory I detected in "Task Manger" grown up to 200MB --> 300 MB (Amazing and suprised !) and my computer "run" slowlier than before ! I don't know why ! Can you help me to explain.

For me, the way to explain in this case is Microsoft SQL Server 2000, he was not able to free memory after each session, it was still in my RAM (and even over my RAM, because there is no room to contain them)! So that, it was grown up in an amazing way !

My solution in this case is use an array of string to compare (not simulate a virtual array)! But, in data type of SP not have "array" !

What can I do now ? Please help me to find the way to solve this problem, thanks for all !

I think the fuzzy lookup and fuzzy grouping transforms in Integration Services work very well for solving problems like the one you described. If you need more details, here is the Integration Services dedicated forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

Hope this helps

|||

Thanx to Bogdan Crivat on your suggest,

I tried to find Integration Services from your link of forum and found out that I can use this thing :

go

sp_configure 'show advanced options', 1

go

reconfigure

go

sp_configure 'max server memory', 400

go

reconfigure

go

sp_configure 'min server memory', 100

go

reconfigure

go

But, It is does not apply to maximize my memory, it is over 400 when I run my program !

Note : I use Microsoft SQL Server 2000 in Windows XP SP2.

Regards,

Tran Quang Phuong.