compare 2 tables and store result in 3rd table??

[VMI]

I have two tables and I want to compare these two tables with a query( ie.
"select * from A where B.key = A.key") and the result will be stored in a
3rd table (table C). is this possible? If necessary, I can create the schema
for the 3rd table (C) since it'll be exactly like table A. But I'm more
interested in being able to store the resulting set into another table.

Thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so, then
I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as A.

Hope this helps.

 

[Bonj]

select

 

[Nicholas Paldino [.NET/C# MVP]]

VMI,

That all depends on what kind of comparison are you doing? Are you
comparing A and B to see if the same keys exist in both tables? Or, where
the keys exist in both tables, the fields are the same?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

You're right about the query (it's been a long time since I've worked with
sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled with
data, and I need to store the data of the resulting set into a separate
table (which is a clone of a).

Thanks.

VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a query(
ie. "select * from A where B.key = A.key") and the result will be stored
in a 3rd table (table C). is this possible? If necessary, I can create
the schema for the 3rd table (C) since it'll be exactly like table A. But
I'm more interested in being able to store the resulting set into another
table.

Thanks.

 

[VMI]

You're right about the query (it's been a long time since I've worked with
sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled with
data, and I need to store the data of the resulting set into a separate
table (which is a clone of a).

Thanks.

VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a query( ie.
"select * from A where B.key = A.key") and the result will be stored in a
3rd table (table C). is this possible? If necessary, I can create the
schema for the 3rd table (C) since it'll be exactly like table A. But I'm
more interested in being able to store the resulting set into another
table.

Thanks.

 

[VMI]

I'm just coparing the keys. For any matching keys between both tables (a &
b), I'll be copying the row from table a into my new table c. So I'll be
using the query you gave me, but I wouldn't know what other instructions I'd
execute it with.

Thanks.

VMI,

That all depends on what kind of comparison are you doing? Are you
comparing A and B to see if the same keys exist in both tables? Or, where
the keys exist in both tables, the fields are the same?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

You're right about the query (it's been a long time since I've worked
with sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled with
data, and I need to store the data of the resulting set into a separate
table (which is a clone of a).

Thanks.

VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a query(
ie. "select * from A where B.key = A.key") and the result will be stored
in a 3rd table (table C). is this possible? If necessary, I can create
the schema for the 3rd table (C) since it'll be exactly like table A.
But I'm more interested in being able to store the resulting set into
another table.

Thanks.

 

[VMI]

This is the code I was initially using:

DataTable table_auditGrid = _table_auditAddress.Clone();
foreach (DataRow row in table_keys.Rows)
{
DataRow[] resultRow = _table_auditAddress.Select("col_Key = '" +
row["key"] + "'");
if (resultRow.Length > 0)
{
table_auditGrid.Rows.Add(resultRow[0]); //error saying that this
row already exists in another table
}
}

VMI,

That all depends on what kind of comparison are you doing? Are you
comparing A and B to see if the same keys exist in both tables? Or, where
the keys exist in both tables, the fields are the same?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

You're right about the query (it's been a long time since I've worked
with sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled with
data, and I need to store the data of the resulting set into a separate
table (which is a clone of a).

Thanks.

VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a query(
ie. "select * from A where B.key = A.key") and the result will be stored
in a 3rd table (table C). is this possible? If necessary, I can create
the schema for the 3rd table (C) since it'll be exactly like table A.
But I'm more interested in being able to store the resulting set into
another table.

Thanks.

 

[Nicholas Paldino [.NET/C# MVP]]

VMI,

Ahh, I see what you are doing now. I would actually query against the
database itself, as the query would be much, much quicker to execute.

However, for what you are doing, that is the right way, with one
exception. Instead of calling the Add method on the DataRowsCollection, you
should call the ImportRow method. This will copy the row into the new
table.

Also, to make it easier (and not have to rely on the Select method), you
might want to establish a DataRelation between the two tables (assuming they
are in the same data set). Once you do that, you can cycle through the rows
of the parent, and then call the GetChildRows method on the row, passing the
DataRelation in. If it returns null, then there are no related records,
otherwise, copy it over.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

This is the code I was initially using:

DataTable table_auditGrid = _table_auditAddress.Clone();
foreach (DataRow row in table_keys.Rows)
{
DataRow[] resultRow = _table_auditAddress.Select("col_Key = '" +
row["key"] + "'");
if (resultRow.Length > 0)
{
table_auditGrid.Rows.Add(resultRow[0]); //error saying that this
row already exists in another table
}
}

VMI,

That all depends on what kind of comparison are you doing? Are you
comparing A and B to see if the same keys exist in both tables? Or,
where the keys exist in both tables, the fields are the same?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

You're right about the query (it's been a long time since I've worked
with sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled with
data, and I need to store the data of the resulting set into a separate
table (which is a clone of a).

Thanks.

in message VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as
A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a
query( ie. "select * from A where B.key = A.key") and the result will
be stored in a 3rd table (table C). is this possible? If necessary, I
can create the schema for the 3rd table (C) since it'll be exactly like
table A. But I'm more interested in being able to store the resulting
set into another table.

Thanks.

 

[VMI]

Thanks for the help. It worked.

Vaughn

VMI,

Ahh, I see what you are doing now. I would actually query against the
database itself, as the query would be much, much quicker to execute.

However, for what you are doing, that is the right way, with one
exception. Instead of calling the Add method on the DataRowsCollection,
you should call the ImportRow method. This will copy the row into the new
table.

Also, to make it easier (and not have to rely on the Select method),
you might want to establish a DataRelation between the two tables
(assuming they are in the same data set). Once you do that, you can cycle
through the rows of the parent, and then call the GetChildRows method on
the row, passing the DataRelation in. If it returns null, then there are
no related records, otherwise, copy it over.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

This is the code I was initially using:

DataTable table_auditGrid = _table_auditAddress.Clone();
foreach (DataRow row in table_keys.Rows)
{
DataRow[] resultRow = _table_auditAddress.Select("col_Key = '" +
row["key"] + "'");
if (resultRow.Length > 0)
{
table_auditGrid.Rows.Add(resultRow[0]); //error saying that
this row already exists in another table
}
}

VMI,

That all depends on what kind of comparison are you doing? Are you
comparing A and B to see if the same keys exist in both tables? Or,
where the keys exist in both tables, the fields are the same?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

You're right about the query (it's been a long time since I've worked
with sql statements).
If I use this statement, where would I need to execute it in order to
compare datatable a and datatable b? Both tables are already filled
with data, and I need to store the data of the resulting set into a
separate table (which is a clone of a).

Thanks.

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote in message VMI,

You can't really say:

select * from A where B.key = A.key

Because you don't know what the set of B is at that point. Are you
trying to check for the existence of the key in another table? If so,
then I would just do this:

select
a.*
from
a
inner join b on a.key = b.key

Since you use the field name "key", I'm assuming that each value is
unique, and therefore a one-to-one relationship exists.

If you perform this select, it will give you the same structure as
A.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have two tables and I want to compare these two tables with a
query( ie. "select * from A where B.key = A.key") and the result will
be stored in a 3rd table (table C). is this possible? If necessary, I
can create the schema for the 3rd table (C) since it'll be exactly
like table A. But I'm more interested in being able to store the
resulting set into another table.

Thanks.

 

[Michael Voss]

I'm just coparing the keys. For any matching keys between both tables (a &
b), I'll be copying the row from table a into my new table c. So I'll be
using the query you gave me, but I wouldn't know what other instructions I'd
execute it with.

For pure SQL, something like

insert into c
(
c.col1,
c.col2,
...
)
values
(
select
a.col1,
a.col2,
...
from
a t1,
b t2
where
t1.key = t2.key
);

might work.

Maybe you'll have to define the join explicitly like

from
a inner join b
on
a.key = b.key

like Nicholas suggested.

What's your database ?