combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jen
Create a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA
sqlsql

combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jenCreate a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA

combining tables

hi,
I recently migrated a database from an old server to a new one. however, one
of the tables didn't copy over all of the data, specifically one column. I
have the missing data on the old server but am unable to restore from a
backup b/c new data has already been input to the new server. is there a sql
statement that will allow me to merge the 2 tables?
table structure:
primary key - list of 3's in sequential order (1,2,3,4, etc.)
need to build a relationship with two columns: journal entry and date
missing data is a column called aatrxsource
thanks,
jenCreate a new table on the new server with the same structure call it
_Temp_YourOriginalTableName
Populate _Temp_YourOriginalTableName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns
Then..
UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableName a inner join _Temp_YourOriginalTableName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL
Drop _Temp_YourOriginalTableName
--
HTH. Ryan
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>|||"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:OzhyzFQIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> hi,
> I recently migrated a database from an old server to a new one. however,
> one
> of the tables didn't copy over all of the data, specifically one column.
> I
> have the missing data on the old server but am unable to restore from a
> backup b/c new data has already been input to the new server. is there a
> sql
> statement that will allow me to merge the 2 tables?
> table structure:
> primary key - list of 3's in sequential order (1,2,3,4, etc.)
> need to build a relationship with two columns: journal entry and date
> missing data is a column called aatrxsource
> thanks,
> jen
>
Without the DDL and table structures of both, you should be able to do an
update based on a join.
Step 1: Add the missing column to the destination table.
ALTER TABLE DestinationTable ADD aatrxsource <datatype>
Step 2: Update the Destination Table by joining to the Source table on the
PK field.
UPDATE d
SET d.aatrxsource = s.aatrxsource
FROM DestinationTable d
JOIN SourceTable s ON d.PKField = s.PKField
Rick Sawtell
MCT, MCSD, MCDBA