Export one row of a table into a different DB table row

[Transam388]

Have SQL 2000 and wish to export only a row of data from a particular table
but not the whole table itself. Once this is completed then we want to
import that data into another DB table and replace the current row data in
that table. Now these rows are not named the same thing so this is not a
standard same table export / import.

I saw some things on the web but they appeard to still be more based on
doing the whole table or based on exporting and importing into identicle
tables even if they are in different DB's. Also this seemed rather code
intensive where each item to be exported was being specified. Is there a
standard Query command to say use this DB and select this table and row then
export the data to this DB, this table, and this row?

Thanks!!

[Transam388]

OK, of course I also should have stated that there are ID numbers associated
with the data exported so I guess we need to somehow match the ID number
between DB tables and then import the matching ID number selected row into
the same ID number and selected row of the other DB.? Thanks!

Post by Transam388
Have SQL 2000 and wish to export only a row of data from a particular table
but not the whole table itself. Once this is completed then we want to
import that data into another DB table and replace the current row data in
that table. Now these rows are not named the same thing so this is not a
standard same table export / import.
I saw some things on the web but they appeard to still be more based on
doing the whole table or based on exporting and importing into identicle
tables even if they are in different DB's. Also this seemed rather code
intensive where each item to be exported was being specified. Is there a
standard Query command to say use this DB and select this table and row then
export the data to this DB, this table, and this row?
Thanks!!

[Todd C]

This can be done in standard T SQL code:

UPDATE d
SET d.col1 = s.col1, d.col2=s.col2, . . . d.colN = s.colN
FROM
<Destination Table> d
INNER JOIN <Source Table> s
ON d.KeyColumn = s.KeyColumn
WHERE
s.KeyColumn = <some value>

(or something similar)
If you are working with data in different databases, (and/or servers), you
may need to fully qualify the table name as
<ServerName>.<Database Name>.<Schema Name>.<Table Name>.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Transam388
OK, of course I also should have stated that there are ID numbers associated
with the data exported so I guess we need to somehow match the ID number
between DB tables and then import the matching ID number selected row into
the same ID number and selected row of the other DB.? Thanks!

Post by Transam388
Have SQL 2000 and wish to export only a row of data from a particular table
but not the whole table itself. Once this is completed then we want to
import that data into another DB table and replace the current row data in
that table. Now these rows are not named the same thing so this is not a
standard same table export / import.
I saw some things on the web but they appeard to still be more based on
doing the whole table or based on exporting and importing into identicle
tables even if they are in different DB's. Also this seemed rather code
intensive where each item to be exported was being specified. Is there a
standard Query command to say use this DB and select this table and row then
export the data to this DB, this table, and this row?
Thanks!!

[Transam388]

Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.

Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".

Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?

Thanks again man!!

Post by Todd C
UPDATE d
SET d.col1 = s.col1, d.col2=s.col2, . . . d.colN = s.colN
FROM
<Destination Table> d
INNER JOIN <Source Table> s
ON d.KeyColumn = s.KeyColumn
WHERE
s.KeyColumn = <some value>
(or something similar)
If you are working with data in different databases, (and/or servers), you
may need to fully qualify the table name as
<ServerName>.<Database Name>.<Schema Name>.<Table Name>.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Transam388
OK, of course I also should have stated that there are ID numbers associated
with the data exported so I guess we need to somehow match the ID number
between DB tables and then import the matching ID number selected row into
the same ID number and selected row of the other DB.? Thanks!

Post by Transam388
Have SQL 2000 and wish to export only a row of data from a particular table
but not the whole table itself. Once this is completed then we want to
import that data into another DB table and replace the current row data in
that table. Now these rows are not named the same thing so this is not a
standard same table export / import.
I saw some things on the web but they appeard to still be more based on
doing the whole table or based on exporting and importing into identicle
tables even if they are in different DB's. Also this seemed rather code
intensive where each item to be exported was being specified. Is there a
standard Query command to say use this DB and select this table and row then
export the data to this DB, this table, and this row?
Thanks!!

[Todd C]

Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.

What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?

Can you sketch out an abbreviated table schema for us?

Thanks
--
Todd C

Post by Transam388
Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

[Transam388]

You appear to have it. Basically this is a set of security DB's that have
user ID's associated to photos. Now this holds true in the DB we want to
update but the table and rows are called different things. I'll try to give
a layout.

DB "A" Source DB
Table "Users"
Row "Photo" is what is to be exported
Row "Matricola" is what needs to be matched on DB "B" so that photos are
associated to the same user ID in the target DB.

DB "B" Target DB
Table "BADGE"
Row "PIN" data to be replaced by that exported from DB "A" based on a match
with
Row "ID" from DB "B" and Row "Matricola" from DB "A".

Thanks again for your help on this Todd!!

Post by Todd C
Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.
What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?
Can you sketch out an abbreviated table schema for us?
Thanks
--
Todd C

Post by Transam388
Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

[Todd C]

Not to belabor the point, but is it that "Photo" is a field NAME in table A
and not the CONTENTS of some field? And it needs to be copied over to a
matching field in table B, right?

If so, are you wanting to update ALL the 'Photos' in B with their correct
'Photo' from table A, or just the one for somebody named 'Matricola'?

You need to look for a way to join the two tables together. Does Mr.
Matricola have an ID in Table A that is the same as his ID (or PIN) in Table
B? Use those two fields as the joining clause. If not, you will need to find
one that is UNIQUE, or build your own mapping table.
--
Todd C

Post by Transam388
You appear to have it. Basically this is a set of security DB's that have
user ID's associated to photos. Now this holds true in the DB we want to
update but the table and rows are called different things. I'll try to give
a layout.
DB "A" Source DB
Table "Users"
Row "Photo" is what is to be exported
Row "Matricola" is what needs to be matched on DB "B" so that photos are
associated to the same user ID in the target DB.
DB "B" Target DB
Table "BADGE"
Row "PIN" data to be replaced by that exported from DB "A" based on a match
with
Row "ID" from DB "B" and Row "Matricola" from DB "A".
Thanks again for your help on this Todd!!

Post by Todd C
Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.
What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?
Can you sketch out an abbreviated table schema for us?
Thanks
--
Todd C

Post by Transam388
Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

[Transam388]

Well I am the one belaboring this...not you!

OK, now if a field is basically what I see at the top of "Open table, return
all rows" I have been calling "Row" in all these posts. Now the "Fields" or
"Rows" do not have matching names but yes they contain the user photos and
that data needs to be updated in B from that which is exported from A. So
totally overwrite the current photo in DB "B" with the updated ones exported
from DB "A".

Now "Matricola" is not a person but a screwed up "Field" or "Row" name in
the source DB for the user ID. So I have user ID 1 through 50 and each has a
photo associated with it in the source and target DB. In the target DB I
want to make sure that the photo is again associated to the correct ID based
on those fields "Matricola" from Source and "ID" on target DB.

This sounds as if it may be getting much more in depth so I may have to
bring in a higher level DBA to just write a script or query that can do this.
again, if you can hang on with my banter and we get this though I owe you!!
Thanks Todd!!

Post by Todd C
Not to belabor the point, but is it that "Photo" is a field NAME in table A
and not the CONTENTS of some field? And it needs to be copied over to a
matching field in table B, right?
If so, are you wanting to update ALL the 'Photos' in B with their correct
'Photo' from table A, or just the one for somebody named 'Matricola'?
You need to look for a way to join the two tables together. Does Mr.
Matricola have an ID in Table A that is the same as his ID (or PIN) in Table
B? Use those two fields as the joining clause. If not, you will need to find
one that is UNIQUE, or build your own mapping table.
--
Todd C

Post by Transam388
You appear to have it. Basically this is a set of security DB's that have
user ID's associated to photos. Now this holds true in the DB we want to
update but the table and rows are called different things. I'll try to give
a layout.
DB "A" Source DB
Table "Users"
Row "Photo" is what is to be exported
Row "Matricola" is what needs to be matched on DB "B" so that photos are
associated to the same user ID in the target DB.
DB "B" Target DB
Table "BADGE"
Row "PIN" data to be replaced by that exported from DB "A" based on a match
with
Row "ID" from DB "B" and Row "Matricola" from DB "A".
Thanks again for your help on this Todd!!

Post by Todd C
Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.
What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?
Can you sketch out an abbreviated table schema for us?
Thanks
--
Todd C

Post by Transam388
Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

[Todd C]

OK. I think we're getting somewhere. Going back to the original resonse, you
can do this with a T SQL statement. Knowing a little about your data
structure now, it should look like this:

Update Badge
SET Badge.Photo = Users.Photo
FROM CEIA.dbo.Users AS Users
INNER JOIN CA.dbo.Badge AS Badge
ON Users.Matricola = Badge.ID

If you are tentative about doint the update, try this first:
SELECT *
FROM CEIA.dbo.Users AS Users
INNER JOIN CA.dbo.Badge AS Badge
ON Users.Matricola = Badge.ID

Good luck!
--
Todd C

Post by Transam388
Well I am the one belaboring this...not you!
OK, now if a field is basically what I see at the top of "Open table, return
all rows" I have been calling "Row" in all these posts. Now the "Fields" or
"Rows" do not have matching names but yes they contain the user photos and
that data needs to be updated in B from that which is exported from A. So
totally overwrite the current photo in DB "B" with the updated ones exported
from DB "A".
Now "Matricola" is not a person but a screwed up "Field" or "Row" name in
the source DB for the user ID. So I have user ID 1 through 50 and each has a
photo associated with it in the source and target DB. In the target DB I
want to make sure that the photo is again associated to the correct ID based
on those fields "Matricola" from Source and "ID" on target DB.
This sounds as if it may be getting much more in depth so I may have to
bring in a higher level DBA to just write a script or query that can do this.
again, if you can hang on with my banter and we get this though I owe you!!
Thanks Todd!!

Post by Todd C
Not to belabor the point, but is it that "Photo" is a field NAME in table A
and not the CONTENTS of some field? And it needs to be copied over to a
matching field in table B, right?
If so, are you wanting to update ALL the 'Photos' in B with their correct
'Photo' from table A, or just the one for somebody named 'Matricola'?
You need to look for a way to join the two tables together. Does Mr.
Matricola have an ID in Table A that is the same as his ID (or PIN) in Table
B? Use those two fields as the joining clause. If not, you will need to find
one that is UNIQUE, or build your own mapping table.
--
Todd C

Post by Transam388
You appear to have it. Basically this is a set of security DB's that have
user ID's associated to photos. Now this holds true in the DB we want to
update but the table and rows are called different things. I'll try to give
a layout.
DB "A" Source DB
Table "Users"
Row "Photo" is what is to be exported
Row "Matricola" is what needs to be matched on DB "B" so that photos are
associated to the same user ID in the target DB.
DB "B" Target DB
Table "BADGE"
Row "PIN" data to be replaced by that exported from DB "A" based on a match
with
Row "ID" from DB "B" and Row "Matricola" from DB "A".
Thanks again for your help on this Todd!!

Post by Todd C
Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.
What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?
Can you sketch out an abbreviated table schema for us?
Thanks
--
Todd C

Post by Transam388
Thank you Todd. If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server. DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

[sql_noob]

Post by Todd C
OK. I think we're getting somewhere. Going back to the original resonse, you
can do this with a T SQL statement. Knowing a little about your data
Update Badge
SET Badge.Photo = Users.Photo
FROM CEIA.dbo.Users AS Users
INNER JOIN   CA.dbo.Badge AS Badge
     ON Users.Matricola = Badge.ID
SELECT *
FROM CEIA.dbo.Users AS Users
INNER JOIN   CA.dbo.Badge AS Badge
     ON Users.Matricola = Badge.ID
Good luck!
--
Todd C

Post by Transam388
Well I am the one belaboring this...not you!
OK, now if a field is basically what I see at the top of "Open table, return
all rows" I have been calling "Row" in all these posts.  Now the "Fields" or
"Rows" do not have matching names but yes they contain the user photos and
that data needs to be updated in B from that which is exported from A.  So
totally overwrite the current photo in DB "B" with the updated ones exported
from DB "A".
Now "Matricola" is not a person but a screwed up "Field" or "Row" name in
the source DB for the user ID.  So I have user ID 1 through 50 and each has a
photo associated with it in the source and target DB.  In the target DB I
want to make sure that the photo is again associated to the correct ID based
on those fields "Matricola" from Source and "ID" on target DB.
This sounds as if it may be getting much more in depth so I may have to
bring in a higher level DBA to just write a script or query that can do this.
 again, if you can hang on with my banter and we get this though I owe you!!  
Thanks Todd!!

Post by Todd C
Not to belabor the point, but is it that "Photo" is a field NAME in table A
and not the CONTENTS of some field? And it needs to be copied over to a
matching field in table B, right?
If so, are you wanting to update ALL the 'Photos' in B with their correct
'Photo' from table A, or just the one for somebody named 'Matricola'?
You need to look for a way to join the two tables together. Does Mr.
Matricola have an ID in Table A that is the same as his ID (or PIN) in Table
B? Use those two fields as the joining clause. If not, you will need to find
one that is UNIQUE, or build your own mapping table.
--
Todd C

You appear to have it.  Basically this is a set of security DB's that have
user ID's associated to photos.  Now this holds true in the DB we want to
update but the table and rows are called different things.  I'll try to give
a layout.
DB "A" Source DB
Table "Users"
Row "Photo" is what is to be exported
Row "Matricola" is what needs to be matched on DB "B" so that photos are
associated to the same user ID in the target DB.
DB "B" Target DB
Table "BADGE"
Row "PIN" data to be replaced by that exported from DB "A" based on a match
with
Row "ID" from DB "B" and Row "Matricola" from DB "A".
Thanks again for your help on this Todd!!

Post by Todd C
Not sure I understand the question.
You are moving data from the Users table in Db CEIA to the Badge table in Db
AC.
What do you mean by rows Photo and PIN? Is there a column in table Users
that has a value of "Photo"? Is there a column in table Badge that has a row
of "PIN"?
Can you sketch out an abbreviated table schema for us?
Thanks
--
Todd C

Thank you Todd.  If I may as this I claim to know not much but I am a little
familiar with the Query Analyzer.
Two Databases on same server.  DB names CEIA and AC.
Now we want from DB CEIA, the Table "Users" and row "Photo" to be placed /
updated into the AC database in table "Badge" and Row "PIN".  
Now here is the catch, we want from CEIA, same table but row "ID" to match
on the target so the photos match where they currently are as far as user ID
on the source DB verus it just puts the photos in the order it read them
regardless of if the ID number matching.?
Thanks again man!!

insert_identity on
select <all columns here> from server.db.schema.table where whatever
insert_identity off

or you can do it through SSIS and there is a check box for identity