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

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com