Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
YogWhat do mean by "Automated"? There is certainly a way, just write a query
that returns a boolean 1/0 based on whether the row column values are the
same or different... How to "Automate" it, depends on what "event" you want
to "trigger", (cause ) it to run... If yo want it to run automatically on
some time interval, use SQLAgent...
If you want it to run whenever someone inserts, updates, or deletes a record
from the table, then use a trugger.
"Yog" wrote:

> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the same
> table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog|||What result do you want from the comparison? Do you mean you want to
compart two particular rows? In that case you can use a self-join on
the columns you want to compare.
Maybe you just want to find duplicates, in which case:
SELECT col1, col2, col3, ...
FROM YourTable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||Yog wrote:
> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the
> same table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog
You can use the CHECKSUM() function as in:
Create table #checktest(col1 int, col2 int, col3 int)
insert into #checktest values (1, 2, 2)
insert into #checktest values (2, 2, 2)
insert into #checktest values (3, 2, 3)
Select CHECKSUM(col2, col3) as "ID 1"
From #checktest
where col1 = 1
UNION ALL
Select CHECKSUM(col2, col3) as "ID 2"
From #checktest
where col1 = 2
UNION ALL
Select CHECKSUM(col2, col3) as "ID 3"
From #checktest
where col1 = 3
drop table #checktest
David Gugick
Imceda Software
www.imceda.com|||To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
"CBretana" wrote:
> What do mean by "Automated"? There is certainly a way, just write a quer
y
> that returns a boolean 1/0 based on whether the row column values are the
> same or different... How to "Automate" it, depends on what "event" you wa
nt
> to "trigger", (cause ) it to run... If yo want it to run automatically on
> some time interval, use SQLAgent...
> If you want it to run whenever someone inserts, updates, or deletes a reco
rd
> from the table, then use a trugger.
> "Yog" wrote:
>|||SELECT A.col1, A.col2, ...
FROM YourTable AS A,
YourTable AS B
WHERE A.x = ?
AND B.x = ? /* Specify the two rows to be compared */
AND
(A.col1<>B.col1
OR A.col1<>B.col1
OR ...)
David Portas
SQL Server MVP
--|||Hi David,
Many thanks for your POST.
Your answer provided me HINT to achieve my goal of comparing two rows for my
requirement.
Regards,
Yog
"David Portas" wrote:

> SELECT A.col1, A.col2, ...
> FROM YourTable AS A,
> YourTable AS B
> WHERE A.x = ?
> AND B.x = ? /* Specify the two rows to be compared */
> AND
> (A.col1<>B.col1
> OR A.col1<>B.col1
> OR ...)
> --
> David Portas
> SQL Server MVP
> --
>

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
Yog
On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:

>Hi Gurus,
>Is there any automated way to compare the contents of TWO rows in the same
>table of SQL Server ?
Hi Yog,
The answer is probably yes - but you've given too little information
about your needs to allow for any more specific information.
Please check out www.aspfaq.com/5006to find out what information is
needed (and in what form) to allow others to understand your problem and
proposes solutions.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Well,
To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
Hope this makes sence.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:
>
> Hi Yog,
> The answer is probably yes - but you've given too little information
> about your needs to allow for any more specific information.
> Please check out www.aspfaq.com/5006to find out what information is
> needed (and in what form) to allow others to understand your problem and
> proposes solutions.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 22:27:02 -0800, Yog wrote:

>Well,
>To precisely put it , the content of the problem is as below
>Objective :- Compare TWO rows contents for few selected Columns. If they are
>different then show those columns with the contents.
>ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
>language or front end)
>Hope this makes sence.
Hi Yog,
Yes - but not enough to provide actual help. I need your table structure
(posted as CREATE TABLE stmt), some sample data (as INSERT stmts) and
the expected output.
I see that the link I posted got mangled - I missed a space between the
link and the next word. Here's the link again:
www.aspfaq.com/5006
Please follow that link and read the information there; then post the
required information here for further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.sqlsql

compare mdf for success restore of data and file storage

Gurus,
I have a scenario where in if i restore a database file(mdf) onto
say some AppManager,i want to make sure its restored properly comparing with
the mdf that it came from(i take this as base).i hope iam making sense here.Ram,
Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
latter do you have the log file as well? If so the database will resolve to
the same schema and data as the database was when it was detached.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
> Gurus,
> I have a scenario where in if i restore a database file(mdf)
> onto
> say some AppManager,i want to make sure its restored properly comparing
> with
> the mdf that it came from(i take this as base).i hope iam making sense
> here.|||Thanks Jerry,
Actually backup file contains the folders database(mdf and logfile) and the
file store.
Ram
"Jerry Spivey" wrote:

> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Jerry,
Back up file has an extension of _dbbak which is compressed and contains
both mdf and ldf files.
"Jerry Spivey" wrote:

> Ram,
> Restore of a backup (.bak) or an attach of a data file (.mdf)? If the
> latter do you have the log file as well? If so the database will resolve
to
> the same schema and data as the database was when it was detached.
> HTH
> Jerry
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4D3E6347-DCCE-49C4-A1DD-B14D0596AB82@.microsoft.com...
>
>|||Ram,
Compressed? Try decompressing the .mdf and .ldf files then copy them to the
location(s) where you store your .mdf and .ldf files. Then use sp_attach_db
to associate the database with SQL Server.
HTH
Jerry
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A4A34557-DEAA-4E69-81B6-5085E78C0830@.microsoft.com...
> Jerry,
> Back up file has an extension of _dbbak which is compressed and contains
> both mdf and ldf files.
> "Jerry Spivey" wrote:
>

Compare data and add to table

Hi all,

Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need):

I have 2 tables.

Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records.

Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers.

I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3.

Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out.

Thanks a lot.

MarkSome app, huh?!

update a set Column3 = b.Column2
from TableA a
inner join TableB b
on a.Column1 = b.Column1|||Thank you, thank you, thank you.

As you can see, I am in not a DB admin, nor do I have the resources on had to find the solution to this problem. (or I would have RTFM) Thanks lots, you have relieved me of quite a bit of stress. Thanks again.

I'd buy you a beer if I could.

Mark|||Heineken, please ;)