Combining two tables from different databases

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?not without creating all new PK values.

if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)

in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.

the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.

DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.

Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run th
e
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want t
o
script the objects using the scripting options in Enterprise Manager or usin
g
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx o
r
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:

> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run
the
> exact same actions on the live server when the time comes.

Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run the
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want to
script the objects using the scripting options in Enterprise Manager or using
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx or
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:
> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run the
> exact same actions on the live server when the time comes.sqlsql

Combining tables from different databases

Hi.

I'm currently working on a project which involves the creation of a
web page which reports selected data to customers from two back-end
systems. I use ASP script language on IIS, the server is MS SQL 2000.
Now I'm struggling with combining two tables from the different
databases. I'm sure it's simple enough, but I'm a little short on the
SQL expertise.

I've got two databases, db1 and db2, and then two tables, db1.t1 and
db2.t2. I need to combine these two tables (both tables have a
matching key field) to make a list of all items from db1.t1, and those
who correspond from db2.t2.

I can list all items from db1.t1, but I can't seem to get the db2.t2
joined in.
Can anybody help me with the syntax for this, please ? Help !

Answers, hints & tips greatly appreciated.
Thanks in advance !
KennethHi

You will need three part naming to do this

Use DB1 -- Connected to DB1!

SELECT t.Fld, s.Fld
FROM t1 t JOIN db2..t2 s ON t.Fld = s.Fld

John

"Kenneth Fosse" <kennethfosse@.hotmail.com> wrote in message
news:a4092994.0310110658.42c8abc7@.posting.google.c om...
> Hi.
> I'm currently working on a project which involves the creation of a
> web page which reports selected data to customers from two back-end
> systems. I use ASP script language on IIS, the server is MS SQL 2000.
> Now I'm struggling with combining two tables from the different
> databases. I'm sure it's simple enough, but I'm a little short on the
> SQL expertise.
> I've got two databases, db1 and db2, and then two tables, db1.t1 and
> db2.t2. I need to combine these two tables (both tables have a
> matching key field) to make a list of all items from db1.t1, and those
> who correspond from db2.t2.
> I can list all items from db1.t1, but I can't seem to get the db2.t2
> joined in.
> Can anybody help me with the syntax for this, please ? Help !
> Answers, hints & tips greatly appreciated.
> Thanks in advance !
> Kenneth|||You need to add it as a linked server then use the fully quaklified name
server.database.ownername.tablename

Look up sp_addlinkedserver in BOL

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Combining tables

We have an application that uses Access databases and archives its data
every year. Then there is the current year's data is an Access database. I
have imported the prior years data into their own table in a SQL database.
I've set up a DTS job to run every day that will import the current Access
database. So, for example, we have tables b2003, b2004 and b2005 in a SQL
database. Now I'm trying to create a report that will be using all these
tables. What I was trying to do was to create a view that included all
these SQL tables. However, I can't think of the SQL code that would join
those tables together. Can anyone help me out?
Thanks.I tried to enter in the follwoing:
SELECT *
FROM b2003
UNION ALL
SELECT *
FROM b2004
UNION ALL
SELECT *
FROM b2005
UNION ALL
But I got this error:
"The Query Designer does not support the UNION SQL construct."
Did I do something wrong?
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
> We have an application that uses Access databases and archives its data
> every year. Then there is the current year's data is an Access database.
> I have imported the prior years data into their own table in a SQL
> database. I've set up a DTS job to run every day that will import the
> current Access database. So, for example, we have tables b2003, b2004 and
> b2005 in a SQL database. Now I'm trying to create a report that will be
> using all these tables. What I was trying to do was to create a view that
> included all these SQL tables. However, I can't think of the SQL code
> that would join those tables together. Can anyone help me out?
> Thanks.
>|||Hello,
You may test the following code in SQL server Query Analyzer:
SELECT * FROM b2003
UNION ALL
SELECT * FROM b2004
UNION ALL
SELECT * FROM b2005
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Joshua,
Was it: "The Query Designer does not ~GRAPHICALLY~ support the UNION SQL
construct"?
If so then it is all right, the designer just won't produce a diagram.
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23b5HtBjrFHA.1172@.TK2MSFTNGP11.phx.gbl...
> I tried to enter in the follwoing:
> SELECT *
> FROM b2003
> UNION ALL
> SELECT *
> FROM b2004
> UNION ALL
> SELECT *
> FROM b2005
> UNION ALL
> But I got this error:
> "The Query Designer does not support the UNION SQL construct."
> Did I do something wrong?
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%23I54A8irFHA.2588@.tk2msftngp13.phx.gbl...
database.
and
that
>

combining sql server instances, need help

I have two sql servers, each with several databases on them. Is there an automated, or way to script, combining these servers into one? I want to create a 3rd server, and put all the databases from both server 1 and 2 on it as a back up percaution. My theory is that on server 1 and 2, I will back those databases up nightly via script, and then on server 3, restore the databases nightly. If either server 1 or 2 dies, I simply put that IP address on server 3, and poof! The databases do not go down (however the backup may be some hours old). That gives me time to troubleshoot the down server, and customers do not suffer downtime. Server 3 would also need to be complete with jobs, full text catalogs, logins, etc...

Has anyone developed a system like this? I'm not a wiz in qa yet, so this will take me some time to develop and refine. If you have any tips, I would appreciate it.

Thanks
TomIt's called Log Shipping in SQL 2000. There are informations in BOL and Microsoft website regarding how to set it up and change primary or secondary role when disaster strike.|||Thanks for your answer.
Keep in mind, I don't just want 1 primary, and 1 secondary server. Actually, I have several sql servers. I would like to have all these databases ready on one single spare sql server. Is this possible with log shipping?

Thanks
Tom|||Remember , log shipping is available in SQl2000 only for SQl 7.0 Servers you hav to manually set it up . If you are moe intrested , let me know and I can send you some sql files . I have L/shipping setup on all of my production servers .. (17 Primary Servers use 9 Standby servers consolidating 2 Server to 1|||aashu:

That would be great! For example, I have 10 sql servers, with 10 databases each. Now I want to set up one sql server as a backup, and put all 100 databases on it. so when one sql server goes down, I just put that IP address on my backup server, and delete the other 90 databases.

Thanks Tom|||PS, they are all sql 2000 servers|||CAUTION! Log shipping is only available in Enterprise Edition of SQL Server. This could be quite costly, depending on how many machines you have...|||Thanks, they are all sql 2000 Enterprise edition servers|||I am attaching files|||Save the file on the the PATH as mentioned in the previous file as synch_data.bat . The previous file creates a job (has to be run on SQl Qry Analyzer) . The step of the job is calling this batch file attached. You will have to check all the physical references according to your servers .
If you need more detail Let me know|||thanks aashu, I will dig into this next week, I really appreciate it.

Tommy|||No Problm .. I will help you out and make it work for you

Combining results from 2 databases in 1 table

Hi, I hope someone will take a moment to answer my question.
I'm combining results from 2 databases. The SQL statement works fine in
Query Analyser using the full names of the database tables (db1.dbo.table1,
db2.dbo.table1). In SRS the dataset executes correctly under the data tab
when using the master table as the shared datasource.
However in the layout tab I dont have access to any of the fields in my
dataset.
Can anyone help me display the contents of the dataset in SRS.
Many thanks for taking the time to answer this.
SteveB.Have you tried clicking the refresh button on the data tab?
"fisab" wrote:
> Hi, I hope someone will take a moment to answer my question.
> I'm combining results from 2 databases. The SQL statement works fine in
> Query Analyser using the full names of the database tables (db1.dbo.table1,
> db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> when using the master table as the shared datasource.
> However in the layout tab I dont have access to any of the fields in my
> dataset.
> Can anyone help me display the contents of the dataset in SRS.
> Many thanks for taking the time to answer this.
> SteveB.|||Silly oversight of mine - thanks for the help.
Is this the best method of getting data from 2 databases (on the same
server)? I assume I'd only use a linked server if the databases were on
different machines.
"David Siebert" wrote:
> Have you tried clicking the refresh button on the data tab?
> "fisab" wrote:
> > Hi, I hope someone will take a moment to answer my question.
> >
> > I'm combining results from 2 databases. The SQL statement works fine in
> > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > when using the master table as the shared datasource.
> > However in the layout tab I dont have access to any of the fields in my
> > dataset.
> >
> > Can anyone help me display the contents of the dataset in SRS.
> > Many thanks for taking the time to answer this.
> > SteveB.|||As far as I know, that's the best way to get data from both databases. You
might post to the sql discussions group and probably get a more informed
response there.
"fisab" wrote:
> Silly oversight of mine - thanks for the help.
> Is this the best method of getting data from 2 databases (on the same
> server)? I assume I'd only use a linked server if the databases were on
> different machines.
> "David Siebert" wrote:
> > Have you tried clicking the refresh button on the data tab?
> >
> > "fisab" wrote:
> >
> > > Hi, I hope someone will take a moment to answer my question.
> > >
> > > I'm combining results from 2 databases. The SQL statement works fine in
> > > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > > when using the master table as the shared datasource.
> > > However in the layout tab I dont have access to any of the fields in my
> > > dataset.
> > >
> > > Can anyone help me display the contents of the dataset in SRS.
> > > Many thanks for taking the time to answer this.
> > > SteveB.

Combining multiple databases into one

Hi all, I have a distributed database environment. I need to bring all the data from the distributed centers into one central database. I have replicated all data from the different centers into their databases at the central location, I now need to be able to view the data as if it were in a single database. I have added a field that identifies the source of the data in all the different databases. I've tried replicating from the diffenrent databases into one, but I just have a feeling there should be an easier way than having another copy of the same data in the same location.

The distributed database structures are similar.

The configuration is

Windows Server 2003
SQL Server 2000 SP4

please help anyone.I had the same issue 5 years ago but ended up redesigning whole system so all regions look at the same database. It took me a year to do this.

The draw back is slowness in remote regions. Like New York - Tokyo.

If you are on the same network you can probably link all servers and no need to replicate data just for this. I do replicate data for disaster recovery only.|||

Quote:

Originally Posted by iburyak

I had the same issue 5 years ago but ended up redesigning whole system so all regions look at the same database. It took me a year to do this.

The draw back is slowness in remote regions. Like New York - Tokyo.

If you are on the same network you can probably link all servers and no need to replicate data just for this. I do replicate data for disaster recovery only.

Bandwidth is also a problem for me and the remote centers are setup to operate in a distributed environment.

Combining databases

Dear Developres,

Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.

at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)?

Thanks in advance.

Hi,

I think the problem is something related to membership expanding. Actually you can add your datatables into ASPNETDB.mdf and in each table , create a primary key (such as UserId or something like that ) which is the foreign key of the aspnet_users. And then, create a view so when the user login, he can get all his files.

Thanks.

|||

Hello Michael,

Thanks for you reply ,actually I have done the same thing but in one file that has stored procedure got problem ad it says:

Cannot insert the value NULL into column 'DateCreated', table ASPNETDB.MDF.dbo.Files'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Can you please tell me whats the problem?

|||

Hi,

The DateCreated field in your database doesn't allow nulls so when you insert the record, don't forget to insert the datacreated value, you can use Date.Now to get the current time.

Thanks.

|||

Actually the error is for something else I dont know why because by the time that it was outside the ASPNET.MDF it works well but as I move inside the new databse it has this error.

Thanks

Bye

|||

Hi,

Try to check if allow-null is checked and generally that is caused by the restraint in your database.

Thanks.

Combining data from several databases

Hello

I'm to familiar with Analysis Services. If my question is not related to this forum, please redirect me to somewhere else.

I have a number of databases of the same structure (for instance 5 databases). I would like to create some reports using data from all of them. For instance if there is a table called Customer I would like to get a report with a list of Customers with the addition of one column that contains the source database.

I'm also concerned about performance. I would prefer if the data of the original databases where copied somewhere else, since they are OLTP databases and it would be good not to be affected in terms of performance by the whole procedure.

Can you please give me some options on how to implement a solution?

Hello!

I would recommend you to build a data warehouse.

With SQL Server 2005 you have all tools you need for that.

SSIS(Integration Services) let you pump and transform data from the five source databases.

When you have a data warehouse finished you can build an Analysis Services(SSAS2005) cube on top of that or query the data warehouse tables directly with reporting services(SSRS2005).

All these poducts, the databas engine, SSIS2005, SSAS2005 and SSRS2005 are included in the same license.

HTH

Thomas Ivarsson

Combining 2 SQL databases

Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:There are a number of ways to skin this cat and there are a number of issues to look out for. Don't use the copy database wizard, it's a real peice of crap and it requires all users to be logged out and all connections to be cut.

Personally I would go into the SQL EM, right click on your database, go to Generate SQL Script and I would script out the database and all objects. Then I would open up the generated file in the QA and change the database name and execute the script.

This is where things get a little sticky. You might have problems with duplicate primary keys because of your 2 sets of tables and any foriegn keys that these primararies populate. Your going to have to figure this one out and without knowing your DB I am going to leave this one to you. Other things you might want to disable or look out for before you move the data are constraints and triggers (especially triggers that insert or update other records).

Once you have all of these little things compensated for, I would then DTS all of the data over from the original 2 DBs.

As for dupes, welcome to the world of the DBA. Your just going to have to clean these up using queries. Regular part of the job.|||OK, I'm really new to MS SQL server....

Whats is SQL EM? And where is the QA? Is there a client SQL version I can use on my workstation to manager all this without having to work on my server? :confused:|||If he's lucky, the databases were designed with GUIDs rather than IDENTITY keys... :)

Couldn't resist! :cool:|||SQL EM is the SQL Enterprise Manager. The QA is the query analyzer. Both of these and Books Online can be installed on your desktop using the installation disks. If you are this new, I would'nt attempt anything as complicated as this until you do a little reading. Buy a book. Take a class. Do something.

But if you insist on moving forward make backups of everything before you start thrashing around.

On your server, and maybe on your desktop you can find these tools by going to Start \ Programs \ Microsoft SQL Server.|||These databases were converted from MS Access. They were originally designed by a company called Tigerpaw Software. They have no support for combining Databases though.|||I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA...aint life grand....|||I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA...aint life grand....

Welcome to Olympus...

Give a sample fo 2 tables that are identical in both databases.

Script them using Enterprise Manager, and post the DDL

Did you install the sql server client side tools?|||I just jumped behind my SQL server ad generated a script off of one of the DBs. It of course created a .sql file which I can use to re-create the tables. So I need to do that for both. How do I get them in the same db...just change the name of the DB it creates and then run both scripts?|||I would size the database first and create the databse via Enterprise Manager.

I would then run the scripts in Query Analyzer.

But are the objects the same or not?

If they are you'll have trouble.

Also, there is really no need to combine them

You can query each from it's own database using their database names

USE Northwind
GO

SELECT * FROM Employees

SELECT * FROM pubs.dbo.Employee|||The objects are all the same structure, but the data if different for the most part. Some customers are in both databases because we employ a lot of jackasses...(excuse me)...nice people...who don't follow directions. There are two many dups to go in a manually delete all of them. Lucky for us the software we use has a utility to combine two records.

So I take the scripts I have and run them in the query analyzer...

And what do you mean there is no need to combine them?|||Did you see the select with the three part name?

But then I guess the application needs to look at only 1 database...right?

How many tables are we talking about?

I would dump and restore 1 database, then use DTS to append the data to their "cousin" tables...|||There are 50+ tables.....

What do you mean by dump and restore with cousins?

And yes the app has to only see one database...sqlsql

Combining 2 databases as one

Hi,
We need to combine 2 databases as one on SQL Server 2000. One database has
wickedly many View tables and the other has more wickedly many Stored
Procedures. What would be the best way to combine them.
YCScript the objects in one of the database and use the script to create the o
bjects in the other
database. And test, test, test. Also see: http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"YC" <asppsa@.hotmail.com> wrote in message news:%23I0KtzDhGHA.1276@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Hi,
> We need to combine 2 databases as one on SQL Server 2000. One database ha
s wickedly many View
> tables and the other has more wickedly many Stored Procedures. What would
be the best way to
> combine them.
> YC
>[/vbcol]

Combining 2 databases as one

Hi,
We need to combine 2 databases as one on SQL Server 2000. One database has
wickedly many View tables and the other has more wickedly many Stored
Procedures. What would be the best way to combine them.
YCScript the objects in one of the database and use the script to create the objects in the other
database. And test, test, test. Also see: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"YC" <asppsa@.hotmail.com> wrote in message news:%23I0KtzDhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We need to combine 2 databases as one on SQL Server 2000. One database has wickedly many View
> tables and the other has more wickedly many Stored Procedures. What would be the best way to
> combine them.
> YC
>sqlsql

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,
Did you try the option of building OLAP cube from those 3 DBs and then generating a Report Model out of the cube. I think that is a clear option.
Thanks,
S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

Combing Multiple Databases for Ad Hoc Reporting

Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.

I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.

Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).

Thanks!

Are those 3 db's on different servers/instances?

If yes, one way is to create linked servers to them, and do a BIG join of the 3 db's

say SELECT * FROM DB1, Server2.DB.dbo.table, Server3.DB.dbo.table WHERE blah blah

you get the idea, use the fully quantified names SERVER_NAME.DB_NAME.OWNER.TABLE_NAME

If they're on the same server/instance, then you can do the same, but omit the SERVER_NAME part and linked server part

That's my suggestion as I can't think of any good ways

|||

Yeah,

My first thought was to do that, but I guess I was hoping for a cleaner way to go about it. Some of these databases are quite large, so I worry about performance.

I have been tasked w/ creating ad hoc reporting that spans these databases "where ppl can report on anything", which seems a bit unrealistic considering the size of the databases involved.

I know that OLAP methods can reduce query speeds drastically, but my problem is that I know so very little about it. I guess my hope was making 3 cubes and linking them as a single datasource...

Thanks for the reply and I guess i will start querying... lol.

Nathan

|||

Hi,

Did you try the option of building OLAP cube from those 3

DBs and then generating a Report Model out of the cube. I think that is

a clear option.

Thanks,

S Suresh

|||I am very new to the OLAP scene, although I am trying ramp up very quickly. I bought the Professional SQL server - Analysis Services 2005 w/ MDX and I am working through that and learning a decent amount (about 60% of the book is "point here and click" and does not explain much of the logic behind). My biggest question though is how to make a cube that spans 3 databases... Do I make 3 individual cubes and then make a cube to report on those 3 cubes? I have not found a place in the "wizards" where I can specify more than 1 datasource for a cube (or maybe I should just use the code-behind?). Does anyone have a good link for this kind of reporting or experience with this issue? Thanks!|||

I have a similar situation. Many copies of the same database structure (1000+) on multiple servers (7).

I need to run reports that reference data in all of the databases or selected groups of databases. Is this candidate for OLAP? We currently create queries using linked servers but as you can imagine - they take forever to run depending on the number of databases included.

We have just started to explore possible solutions. The real problem is that the reports need to run against real time data so I am assuming that whatever solution is proposed, it will have to connect to all of the current databases.

Any suggestions or comments would be appreciated!!

Michael

|||

Has anyone had this situation? I would have thought that it would have been common with large retail type organizations. If I had 200 retail locations around the country all running local databases of inventory but I wanted to be able to query all of them at anytime for current inventory status - how would I do it? I need the solution to be real time and not take a long time for the result set.

Thanks

sqlsql

Combine two stored procedure results

Hi,
I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.

STored procedure CallGetdata
@.startdate datetime
@.enddate datetime
If startdate < 1/1/2007 the call getdata in the DB2006
if startdate <1/1/2006 then call getdata in the DB2005.
Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.
I have one idea i.e create a temp table and insert the two Stored procedure results into it.
Create #table1(name varchar(20))
insert into #table1 exec DB2006.dbo.getdata
insert into #table1 exexc DB2005.dbo.getdata
Select * from #table1
drop table #table1.
Anyone please give me better idea than creating temp table.

Thanks in advance

Can you change getdata (or is this something your stuck with?)

Another possible option is to turn your getdata stored procedure into a table valued function. This means you won't be able to exec it directly though, you will have to access it in a query. Then you could do:

select * from db2006.dbo.getdata(...) union select * from db2005.dbo.getdata(...).

This would most likely perform better (although I can't say for sure). If getdata() is one query (or can be turned into one query), then you can use an inline table valued function, and this will definitely perform better then the temp table solution.

Also, your probably better off using a table variable in this case as opposed to a temp table.

|||

if you are stuck with the stored procedure as-is, then your idea is the best one. Unless the proc is extremely complex (and with a name like getdata, it is not going to be easy for us to guess :) then building a database for queries that span databases is a better idea and union the results together. Or consider the ideas that Adam has given also.

I would consider not having databases with the year in the name, and just have one database that spans years, personally. That is clearly the most solid answer and will make your reporting easier. If this wasis a performance idea, there are ways to make this work far better than with multiple databases. And if both databases are on the same drive, you are possibly not saving much...

|||

Thank you very much for your ideas.For the reports the stored procedures already created.But now to imrove the performance they created the separate 3 databases one for current year and other for previous year and remaining(all previous years are in Hist databases).Now I need to migrate the existing stored procedure to all databases all working fine but the problem is when they enter startdate which is in one year and end date in another year, in this case we need to combine the results of two stored procedures from two databases.Thatswhy I created a separate stored procedure and temp table is used for combining the two SP results.

Thanks

|||

Hi,

Which one give better performance whether the Stored procedure with table datatype to insert the combined results from two databases, Or table valued functions.

Thanks.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613
In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613
|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John
|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.
|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.
sqlsql

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
If not, what is the best way to accomplish this?
Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
-Dave
Rodjk #613 wrote:
> Hello,
> I am working with a database that is in use in several locations.
> The structure of the databases are identical, but the data is
> different.
> I am tasked with combining the data into one large database.
> (It is SQL 2000)
> I have .bak files for each location.
> Is there any way to restore a backup into a database, combining the
> data?
> If not, what is the best way to accomplish this?
> Thanks,
> Rodjk #613
>
-Dave Markle
http://www.markleconsulting.com/blog|||Dave Markle wrote:
> In a word, no. You should probably restore both databases to a single
> machine, and write a script to combine the data.
> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
> -Dave
> Rodjk #613 wrote:
>
> --
> -Dave Markle
> http://www.markleconsulting.com/blog
Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.
My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.
I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.
Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?
Thanks
Rodjk #613|||Hi

> Hello,
> Thanks, I kinda figured that about the backups.
> About writing the script, what is involved in that?
> I am pretty good with this one database, but am by no means an expert.
> Luckily, the database is the same version, the same structure at each
> location.
> My thoughts are to add additional blank SQL databases, then populate
> them with the bak files.
> then combine the databases into one large database.
> I understand that a MoveTo is the command, but I have never written one
> and have no idea how to start.
> Any information would be appreciated.
> Am I foolish to hope that since the databases have the same tables and
> structure that this will not be very complicated?
> Thanks
> Rodjk #613
>
You have not said if the combined database will have an extra identifiers to
say which database the data originated from. Also you if you have any
identity columns you will need to decide what you are to do with clashing
identities if they are used as foreign keys.
If there are no foreign keys and the structures are the same then you could
just do
exec sp_msforeachtable 'INSERT INTO ? SELECT * FROM [Otherdb].?'
If there are FKs or identity values you can use
exec sp_msforeachtable 'SELECT ''INSERT INTO ? SELECT * FROM [Otherdb].?
'''
to get a script that you can then manipulate so it is in an order that will
satify the FK constraints and you can add SET IDENTITY_INSERT statements.
You may also want to look at http://vyaskn.tripod.com/code.htm#inserts
If you have a very large amount of data you may want to consider using BCP
instead.
John|||> There are tools to compare the data in the two databases (Like Red
> Gate's SQL Data Compare), but AFAIK nothing to combine the data.
There is a tool to *compare* data - SQL Server Comparison Tool.
Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.
If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.

Combine two databases

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.

There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.

-Dave

Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)
>
I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?
>
If not, what is the best way to accomplish this?
>
Thanks,
Rodjk #613
>

--
-Dave Markle

http://www.markleconsulting.com/blog|||Dave Markle wrote:

Quote:

Originally Posted by

In a word, no. You should probably restore both databases to a single
machine, and write a script to combine the data.
>
There are tools to compare the data in the two databases (Like Red
Gate's SQL Data Compare), but AFAIK nothing to combine the data.
>
-Dave
>
Rodjk #613 wrote:

Quote:

Originally Posted by

Hello,
I am working with a database that is in use in several locations.
The structure of the databases are identical, but the data is
different.
I am tasked with combining the data into one large database.
(It is SQL 2000)

I have .bak files for each location.
Is there any way to restore a backup into a database, combining the
data?

If not, what is the best way to accomplish this?

Thanks,
Rodjk #613

>
>
--
-Dave Markle
>
http://www.markleconsulting.com/blog

Hello,
Thanks, I kinda figured that about the backups.
About writing the script, what is involved in that?
I am pretty good with this one database, but am by no means an expert.
Luckily, the database is the same version, the same structure at each
location.

My thoughts are to add additional blank SQL databases, then populate
them with the bak files.
then combine the databases into one large database.

I understand that a MoveTo is the command, but I have never written one
and have no idea how to start.
Any information would be appreciated.

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Thanks
Rodjk #613|||Rodjk #613 wrote:

Quote:

Originally Posted by

Am I foolish to hope that since the databases have the same tables and
structure that this will not be very complicated?

Yes.

Imagine database A has order no 1, being shipped to customer no 1.
Database B also has an order no 1, being shipped to customer no 1.

But in database A customer no 1 is Mr. Black, in B it is Mr. White.

/jim|||There are tools to compare the data in the two databases (Like Red

Quote:

Originally Posted by

Gate's SQL Data Compare), but AFAIK nothing to combine the data.

There is a tool to *compare* data - SQL Server Comparison Tool.

Dariusz Dziewialtowski.|||Red Gate's SQL Data Compare will let you syncronize your data.
Assuming that you don't have duplicate keys and so on, you can just use
it to bring across data that is in one database and not in another.

If you do have duplicate keys (and you probably do) you're going to
have to do some analysis. If the keys are identity keys I think Red
Gate's product (and they're reasonably cheap) will give you options to
deal with it. If it's a natural key you'll need to do some updating
(say, Update table2 set key = key + 100000) to ensure integrity.