Concatenate

In the SQL Query.. how do i Concatenate ?

I want to concatenate 2 of the same column and table..

example...
tableA with column id and y
tableB with column id and x and group

SELECT d.y + dd.y as PERM
FROM tableA d, tableA dd, tableB c
WHERE d.id = c.id
AND dd.id = c.groupThis is a very very difficult thing to figure out how to do.

One approach would be to look at the online documentation. While this requires advanced skills, I will try to walk your through it.

1) Open Internet Explorer (or your favorite browser)
2) Navigate to http://msdn.microsoft.com
3) Enter in a search of "sql concatenate"
4) Click on the first hyper link provided.
5) Read the page that is displayed.

In case #1 through #4 are too difficult, click here (http://msdn2.microsoft.com/en-us/library/aa276862(SQL.80).aspx) (the blue underlined part is what you click on).

In case reading a page of material is too time consuming:

USE pubs
SELECT (au_lname + ', ' + au_fname) AS Name
FROM authors
ORDER BY au_lname ASC, au_fname ASC

And finally in case the sample is too hard to follow....

Just put parenthesis around the expression!!!!!!|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name

Those two look exactly the same to me... (but I have been here 19+ hours...)|||hhahaa...
i see... hmmm...

the difference is instead of Lname and Fname...

i want both Lname...

your not sleepy huh ?sqlsql

Concat instead of SUM when grouping results

Hello,

I have a very simple problem which I will illustrate with an example:

I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K

I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K

So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?

Kind regards,

Bart WarnezHi Bart,

I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:

CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))

INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'

SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3

DROP TABLE test

which seems to work :)

Good luck!
J|||On 23 nov, 12:52, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
>
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
>
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
>
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
>
DROP TABLE test
>
which seems to work :)
>
Good luck!
J

Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.

Kind Regards,

Bart|||I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...

create function dbo.fn_groupIt(@.test1 varchar(5),@.test3 varchar(5))
returns varchar(5000)
as
begin
declare @.out varchar(5000)
select@.out = coalesce(@.out + ',' + convert(varchar,test2),
convert(varchar,test2))
fromtest
wheretest1 = @.test1 and
test3 = @.test3

return @.out
end

selecttest1, dbo.fn_groupIt(test1,test3) test2,test3
from(
selecttest1,test3
fromtest
group by test1,test3
) a|||Hi Bart,

What spec server are you using? I can run either script in under a
second :-/

J|||On 23 nov, 15:46, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
What spec server are you using? I can run either script in under a
second :-/
>
J

Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!

Bart|||>I guess there is no function to do this... What is the simplest solution? <<

Do it in the front end instead violating 1NF in the Database side.|||On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest solution? <<

>
Do it in the front end instead violating 1NF in the Database side.

Hi,

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.

Kind regards,

Bart|||"Bart op de grote markt" <warnezb@.googlemail.comwrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I guess there is no function to do this... What is the simplest
>solution? <<

>>
>Do it in the front end instead violating 1NF in the Database side.

>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

You're "wrong".

You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.

If you design your database properly, your queries follow from that.

Quote:

Originally Posted by

>
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Quote:

Originally Posted by

>
Kind regards,
>
Bart

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.

This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?

J|||On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"Bart op de grote markt" <warn...@.googlemail.comwrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
>

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest
solution? <<

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Do it in the front end instead violating 1NF in the Database side.

>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.

I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.

And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )

Quote:

Originally Posted by

Quote:

Originally Posted by

I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.

>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>

As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.

Kind regards and thx for all your comments

Bart|||You're "wrong".

Actually Greg - You're "wrong".

SQL Server is a data engine and not just a relational data storage method.

There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.

For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.

There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Complex sql update

Hi,
I'm trying to update a key in tablea with tableb using the where there
are mulitple where criteria. I'm trying to avoid the sql cursors to do
update for each record.
Is there a way I can do that in the following statement:
update billing_detail_debit_card set bdp_id =
(
select b.bdp_id from billing_detail_participant b join
billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
)
where billing_detail_debit_card.billing_proc_no
(
select dc.billing_proc_no, dc.cust_no, dc.company_no,
dc.participant_id
from billing_detail_participant b join billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
) bdp_table
= bdp_table.billing_proc_no
and billing_detail_debit_card.cust_no = bdp_table.cust_no
and billing_detail_debit_card.company_no = bdp_table.company_no
and billing_detail_debit_card.participant_id = bdp_table.participant_id--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You're working overtime. You had it, but then added too much. Try
this:
UPDATE billing_detail_debit_card
SET bdp_id =
(SELECT bdp_id
FROM billing_detail_participant b
WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
AND cust_no = billing_detail_debit_card.cust_no
AND company_no = billing_detail_debit_card.company_no
AND participant_id = billing_detail_debit_card.participant_id )
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
vGO7K01Trgrw9KG7UHn7Kw2q
=XJiT
--END PGP SIGNATURE--
dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to update a key in tablea with tableb using the where there
> are mulitple where criteria. I'm trying to avoid the sql cursors to do
> update for each record.
> Is there a way I can do that in the following statement:
> update billing_detail_debit_card set bdp_id =
> (
> select b.bdp_id from billing_detail_participant b join
> billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> )
> where billing_detail_debit_card.billing_proc_no
> (
> select dc.billing_proc_no, dc.cust_no, dc.company_no,
> dc.participant_id
> from billing_detail_participant b join billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> ) bdp_table
> = bdp_table.billing_proc_no
> and billing_detail_debit_card.cust_no = bdp_table.cust_no
> and billing_detail_debit_card.company_no = bdp_table.company_no
> and billing_detail_debit_card.participant_id = bdp_table.participant_id
>|||Be careful with this. It updates every row of billing_detail_debit_card,
which might result in setting bdp_id to NULL for many
rows in billing_detail_debit_card you don't wish to update,
since if the subquery does not yield any rows, its value will
be NULL.
UPDATES like this can often benefit from SQL Server's
proprietary UPDATE .. FROM syntax, which might look
something like this for the query you have:
UPDATE billing_detail_debit_card
FROM billing_detail_participant AS b
WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
AND b.cust_no = billing_detail_debit_card.cust_no
AND b.company_no = billing_detail_debit_card.company_no
AND b.participant_id = billing_detail_debit_card.participant_id
Steve Kass
Drew University
"MGFoster" <me@.privacy.com> wrote in message
news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> You're working overtime. You had it, but then added too much. Try
> this:
> UPDATE billing_detail_debit_card
> SET bdp_id =
> (SELECT bdp_id
> FROM billing_detail_participant b
> WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND cust_no = billing_detail_debit_card.cust_no
> AND company_no = billing_detail_debit_card.company_no
> AND participant_id = billing_detail_debit_card.participant_id )
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
> vGO7K01Trgrw9KG7UHn7Kw2q
> =XJiT
> --END PGP SIGNATURE--
>
> dmalhotr2001@.yahoo.com wrote:|||or you can add a where exists clause to update on rows where a matching row
exists.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Be careful with this. It updates every row of billing_detail_debit_card,
> which might result in setting bdp_id to NULL for many
> rows in billing_detail_debit_card you don't wish to update,
> since if the subquery does not yield any rows, its value will
> be NULL.
> UPDATES like this can often benefit from SQL Server's
> proprietary UPDATE .. FROM syntax, which might look
> something like this for the query you have:
> UPDATE billing_detail_debit_card
> FROM billing_detail_participant AS b
> WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND b.cust_no = billing_detail_debit_card.cust_no
> AND b.company_no = billing_detail_debit_card.company_no
> AND b.participant_id = billing_detail_debit_card.participant_id
> Steve Kass
> Drew University
>
> "MGFoster" <me@.privacy.com> wrote in message
> news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
>|||Sorry, forgot the code...
update billing_detail_debit_card
set bdp_id =
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
where exists
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:Ol9$tdUkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> or you can add a where exists clause to update on rows where a matching
row
> exists.
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
billing_detail_debit_card,[color=darkred
]
there
do
bdp_table.participant_id
>

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.c...pare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/ar...11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
Jason
Hello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>
|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.
|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com