MS SQL Server - combining tables

Author

combining tables

Jennifer Mar

2006-01-24, 11:23 am

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

Ryan

2006-01-24, 11:23 am

Create a new table on the new server with the same structure call it
_Temp_YourOriginalTa
bleName

Populate _Temp_YourOriginalTa
bleName from old server via DTS / BCP. You only
need populate Primery Key and aatrxsource columns

Then..

UPDATE a SET a.aatrxsource = b.aatrxsource
FROM YourOriginalTableNam
e a inner join _Temp_YourOriginalTa
bleName b
on a.PrimaryKey = b.PrimaryKey
WHERE a.aatrxsource IS NULL

Drop _Temp_YourOriginalTa
bleName

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

Rick Sawtell

2006-01-24, 11:23 am

"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

Sponsored Links