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

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

sqlsql

COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.

Collations Problem

I have recently migrated a SQL Server 6.5 DB to SQL 2000.

On a particular table i added a new varchar ( [field29] - see below)

now when changing a record in this table, the performance is greatly reduced.
In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :

"the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".

The table has 300,000 records. The update takes about 20secs.

After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.

Is this related to Collations?
([Field1] is the Primary Key)

any ideas?

CREATE TABLE [dbo].[Tabletest]
(
[field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field2] [smallint] NOT NULL ,
[field3] [datetime] NOT NULL ,
[field4] [datetime] NULL ,
[field5] [datetime] NULL ,
[field6] [datetime] NULL ,
[field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field12] [smallint] NOT NULL ,
[field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field15] [smallint] NULL ,
[field16] [smallint] NULL ,
[field17] [smallint] NULL ,
[field18] [smallint] NULL ,
[field19] [smallint] NULL ,
[field20] [smallint] NULL ,
[field21] [smallint] NULL ,
[field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field23] [smallint] NOT NULL ,
[field24] [bit] NOT NULL ,
[field25] [datetime] NULL ,
[field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field27] [int] NOT NULL ,
[field28] [smallint] NULL ,
[field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
)Use WHERE clause, you do not need to see 300,000 records when you are changing one :)