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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...

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.

[Bonj]

> select

a.* into c from
a
inner join b on a.key = b.key

[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.

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:ei**************@TK2MSFTNGP11.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...

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,

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:ev**************@TK2MSFTNGP10.phx.gbl...

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]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ei**************@TK2MSFTNGP11.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...

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.

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:eU****************@TK2MSFTNGP10.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:ev**************@TK2MSFTNGP10.phx.gbl...

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]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ei**************@TK2MSFTNGP11.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...
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
}
}

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:eU****************@TK2MSFTNGP10.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:ev**************@TK2MSFTNGP10.phx.gbl...

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]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ei**************@TK2MSFTNGP11.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...
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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:Ok**************@tk2msftngp13.phx.gbl...

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
}
}

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:eU****************@TK2MSFTNGP10.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:ev**************@TK2MSFTNGP10.phx.gbl...

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]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ei**************@TK2MSFTNGP11.phx.gbl...
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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:eQ**************@TK2MSFTNGP14.phx.gbl...
>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
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:ew**************@TK2MSFTNGP15.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:Ok**************@tk2msftngp13.phx.gbl...

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
}
}

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:eU****************@TK2MSFTNGP10.phx.gbl...

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]
- mv*@spam.guard.caspershouse.com

"VMI" <vo******@yahoo.com> wrote in message
news:ev**************@TK2MSFTNGP10.phx.gbl...
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]" <mv*@spam.guard.caspershouse.com>
wrote in message news:ei**************@TK2MSFTNGP11.phx.gbl...
> 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]
> - mv*@spam.guard.caspershouse.com
>
> "VMI" <vo******@yahoo.com> wrote in message
> news:eQ**************@TK2MSFTNGP14.phx.gbl...
>>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]

VMI wrote:

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 ?