Relating Tables From Different Databases
Nov 29, 2006
Hi,
I am trying to relate two tables but both are from different databases. I have a database 'Current' which has a table 'case' and another database 'Org' which has a table 'employee'. I want to establish a one-to-many relationship between employee and case. I created a coulmn 'employee_key' in the case table & entered values matching the pkey of the employee table. Then I wrote a query using joins to access the table records.
select Org..employee.pkey, Current..case.assignedengineer, Current..case.pkey from Current..case join Org..employee on Current..request.employee_key=Org..employee.pkey
Although the above query works fine, there is no relation between the two tables of the two databases, I wanted to know if this is the right way to achieve what i want to or is there a way in which i can actually create a relationship between tables of two different datbases. Can anyone suggest/help???
Also, I wanted to know if i can relate 1 table of SQL Server db with another table of Oracle DB. Please help ...........
Thanks
View 1 Replies
ADVERTISEMENT
Mar 18, 2013
Is it possible to relate two tables (in the same db) using the PK (int) from one table to a UK (int, allownulls=No) from another?
I've tried, but had no success. Both tables are empty, so there is no way of having a null in the UK column that would prevent relating it to a PK. Just wanted to know if such relation is even possible before spending additional time on figuring out how to do it...
View 14 Replies
View Related
Nov 18, 2015
I'm currently setting up a Tabular Model to do some research between several fact tables. In this example i have two fact tables (table 1 and table 2) which I've created a 1 to 1 relationship on phone number. Typically I create a relationship between these tables to find common data between the two. However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
View 3 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Nov 27, 2007
Hi,
I have couple of DBF files.. and when i get them into my scrub database the datatype for all the fields in char. but after i scrub the data i put them into a money feild.. so that if so that i can do any calculations that i want in the report...
Suppose if i keep as a char or varchar in my production database... is it possible to any calculation on it in my report.. just simple additions... and if yes... do i need to convert them into anything before doing the additions... The reason i am asking this is Some of the fields in the scrub database are empty and i want to keep them the same in my production database instead of showing 0.00 as a default.
Any help will be appreciated.
Regards
Karen
View 1 Replies
View Related
May 9, 2008
hello experts
i have two sql select statements that selects data from two different tables. I want to concatenate these two sql queries into one singl query (through programming only not through joins). After concatenation i want to sent the single created query to my transection function so that it can select data from two different tables.
How can i acomplish this in vb .net or c#
thanks
fine regards
ali
View 2 Replies
View Related
Jun 10, 2008
I am attempting to determine if a record for TODAY has been written. If one, I update it. If no records for TODAY have been created yet, I INSERT a new one. I am basing my "if" (C#) on a Select count(*) WHERE as shown below. My problem is that I do not know how to obtain the result. The debugger displays a -1 as the count and the IF falls through to its companion ELSE. Can anyone please set me straight? Oh, I build 'TestDate' from getdate and it ends up as a str like "6/10/2008". The Date column is also a varchar with the same kind of string as its contents.String fpsqlA = ("SELECT count(*) from DailyCounts WHERE Date = '"+testDate+ "'");
SqlCommand fpcmdA = new SqlCommand(fpsqlA, connStringDaily);fpcmdA.CommandType = CommandType.Text;
int iCount = fpcmdA.ExecuteNonQuery();SqlDataAdapter ad1 = new SqlDataAdapter(fpcmdA);
ad1.Fill(dtA);
//Test to see if a record for 'today' has already been written to the DailyCounts table. if (iCount <= 0)
{
View 6 Replies
View Related
Apr 9, 2008
Hi all,
I am still relatively new to the SQL world, and have just started looking at purging data from my databases which is older than 8 years old. I am using a routine from another forum on here to get a before Table size pre purge, and then running it again to get the new size after.
The thing i have found is that even though i have deleted over 1.5 million rows, there is actually no difference in the table size.
I have been looking at the UPDATE STATISTICS on the msdn web pages to see if i need to run this to update the information i am using for size comparisons, but it doesnt really tell me if this is one of the things that it updates, and was wondering if i was being dumb and missing something completely obvious here. The other thing i can think of is that even though the data has been deleted, the space has been retained in the indexes on the tables, and until i do a re-index i will not realise the space savings?
Many thanks in advance, and feel free to point and snigger if i have just missed something staring me in the face
Mark
View 1 Replies
View Related
Aug 13, 2007
hi!
i have some what a definition table of the fields of the other table
name as DefinitionTable with data as follows
FieldType FieldName1 FieldDesc TxtColor FontStyle
8 FIELD1 ABC #1111 [fsbold]
8 FIELD2 CDE #2222 [fsitalic]
8 FIELD3 EFG #3333 [fsbold]
OrigTable
OrigKey FieldType FIELD1 FIEDL2 FIELD3 Results
1 8 test1 test2 test3 ok
so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..
my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is
create procedure Definition
@SearchKey int,
@FieldTypeint
as
begin
DECLARE @FieldName varchar(10),
@FieldDesc varchar(30),
@TextColor int,
@mysql varchar(4000)
SET NOCOUNT ON
SELECT @mysql = 'select o.origkey, '
DECLARE MyCursor CURSOR READ_ONLY FOR
SELECT FieldName1, FieldDesc FROM DefinitionTable
WHERE FieldType = @ResultType
Open MyCursor
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
WHILE @@FETCH_STATUS = 0
BEGIN
select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', '
FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc
END
CLOSE MyCursor
DEALLOCATE MyCursor
select @mysql = @mysql + ' o.results from OrigTable o where
FieldType = ' + @ResultType
execute(@mysql)
end
the result is Ok as
OrigKey ABC CDE EFG Result
8 test1 test2 test3 ok
But the problem is how to include the TxtColor or concatenate TxtColor with this output below
OrigKey ABC CDE EFG Result
8 test1+##1111 test2+#222 test3+#3333 ok
thanx,
mygt
View 1 Replies
View Related
May 20, 2008
EDIT: Please skip over this and go to the 2nd post. After writing this, I realized it may be too complicated to comprehend, so I tried to simplify with another example which is found in the 2nd post. Or if you want you can read this one and try to understand it.
I would like to go over a design we came up with to handle a certain reporting requirement from an OLAP cube to see if this is a good approach. Also, I would like to hear about other designs that we can consider.
Here is the condensed version of the requirement.
We are measuring the performance of a bunch of portfolios. So among other dimensions, we have a portfolio dimension that holds the list of portfolios we are tracking. In a typical portfolio analysis, an analyst almost always looks at a portfolio's measures along side a benchmark portfolio's measures. A benchmark is another portfolio against which a selected portfolio is compared. So, for a given portfolio, there is a determined benchmark portfolio.
For example, an analyst might want to look at a measure called Annual_Yield for a portfolio called Portfolio_A. There will be an attribute on the member Portfolio_A that tells us what its benchark portfolio is. That attribute will basically be another member from the portfolio dimension. So, as a function of what an analyst chooses as the first portfolio (the subject portfolio), the cube should be smart enought to know what to select as the second portfolio (the benchmark portfolio).
To implement this, I am proposing the following structure for the table that will create the portfolio dimension.
PortfolioID, PortfolioName, Attr1, Attr2, ..., AttrN, BenchMarkID
1, Portfolio_A, blah, blah, ...., blah, 3
2, Porfolio_B, blah, blah, ...., blah, 3
3, Portfolio_C, blah, blah, ...., blah, null
4, Portfolio_D, blah, blah, ...., blah, 3
5, Portfolio_E, blah, blah, ...., blah, 6
6, Portfolio_F, blah, blah, ...., blah, null
So, the above data says that Portfolios A, B, and D uses Portfolio C as their benchmarks. And Portfolio E uses Portfolio F as its benchmark. Lastly, if a portfolio is used as a benchmark for other portfolios, then its BenchmarkID is null, meaning it doesn't get compared to anything, thus Portfolios C and F have null BenchmarkIDs. With this data, I would create a hierarchy in the portfolio dimension that has two levels. At the top level, there will be a portfolio and if that portfolio has a benchmark, then it will drill down to it.
With a portfolio dimension designed as such, I envision a fact table that looks like the following.
PortfolioID, SomeOtherDimIDs,....., Annual_Yield
1, , blah, ,....., 10%
2, , blah, ,....., 7%
3, , blah, ,....., 0%
4, , blah, ,....., 15%
5, , blah, ,....., 20%
6, , blah, ,....., 9%
So, going back to the original report which was looking at Annual_Yield by Portfolio_A, I would do an MDX statement such as
With Set [PortfolioAndBenchmark] As
DESCEDANTS(Portfolio_A)
Select
[Annual_Yield], [PortfolioAndBenchmark] On Columns,
Whatever Other Dimension On Rows
From
[Cube]
Does this make sense? The only concern I have is that I don't want the measures to aggregate along the portfolio hierarchy.
View 1 Replies
View Related
Nov 23, 2007
Our mobile application using VS2005 with VB.net is ready and we are trying to set up the SQL CE on the server to test the remote connection procedures. Sql 2005 is based on a dedicated server and is accessed via the Web server which does not have Sql installed on it. We have installed the Sql Server Me Server Tools on the Web server and configured the Virtual directory with IIS. What we cannot get our heads around is how we link the database on the separate Sql server via the web server. When the Pda contacts the Web server via IIS we somehow need to redirect to the dedicated Sql server as it cannot be contacted outside of our network. We have been told that Sql Ce can link with an existing Sql 2005 database, how can we do this?
View 6 Replies
View Related
Apr 2, 2006
I've been searching everywhere for a solution to this problem and no answers exist anywhere. When I try to create a new maintenance plan I get the following error. I've been told it may be related to SSIS but nobody has a solution. How do I fix this issue so I can create a maintenance plan.
Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
An OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages. A SQL statement was issued
and failed.
View 1 Replies
View Related
Apr 14, 1999
Hi,
I'm writing an intranet application - a web survey engine.
I'd like to create a new database for each user that creates a survey form...
versus creating new tables for each survey.
Memory and disk space is not exactly the problem... just user permissions. If I dump all the results onto one database, doesn't it mean that this user can have access to all other tables(including the results of other surveys??)
Question is, does SQL Server reserve a bunch of memory for each database that I create, thereby hogging up a lot of space if I create more than one database.
Thank you.
View 1 Replies
View Related
Apr 25, 2008
hi all,
i want to write SQL query that create table and insert in this table all databases' name and their tables' name that exist in my server.
e.g
DB name Table name
------- ----------
Northwind product
Northwind customers
Northwind Employees
Pubs authors
Pubs discounts
Pubs employee
msdb log_shipping_databases
msdb log_shipping_monitor
View 3 Replies
View Related
Mar 11, 2002
Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)
The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:
DECLARE @SQL NVarchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL
PRINT @SQL -- Only if you want to see the code you're about to execute.
EXEC (@SQL)
Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.
------------------------
GENERAL-ly speaking...
View 2 Replies
View Related
Feb 6, 2007
Hi All,
Is it possible to join 2 tables from 2 different databases in Stored Procedure.
Thanks in advance.
View 2 Replies
View Related
Aug 3, 2007
hi,
i have one database ASPNETDB.MDF created by default when adding a user to my site, and MyData.mds - my database...i want to join the aspnet_Users table with another table created by me (in myData.mds), how can i do that? is hard if i should re-write all the data from myData into the ASPNETDB,i even writed both connectionStrings in the web.config but still with no succes...
is there any trick in the SQL statment? please help me
thank you
View 10 Replies
View Related
Dec 21, 2007
Hello,
Let's say I created some tables in SQL server using the designer. Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table? Or how would I send somebody else the tables?
Thanks.
View 3 Replies
View Related
Jan 23, 2008
I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application.
To refer to the two databases I have used the following
DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable)
For both databases I get the error "Invalid object name"
What am I missing?
Thanks
View 2 Replies
View Related
Jan 2, 2006
Hi :)
I would like to know if it is possible to link two databases together
(in my case ASPNETDB, and another mdf database) so that I can run
queries on those shared tables. For example, I would like to use the
uniqueidentifiers from the ASPNETDB tables in my own tables.
Thank you!
(I do use the latest version of Visual Web Developer).
View 3 Replies
View Related
Dec 16, 2001
I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me
View 1 Replies
View Related
Apr 23, 2007
Hey everyone. I am somewhat of a newbie to the database world.
I have been given the task of connecting two different SQL databases. Both are Microsoft SQL.
I want some of the tables of one of the databases to be linked to the tables of the other.
Is this possible?
Thanks
RoadHired
View 1 Replies
View Related
Jul 2, 2004
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?
View 4 Replies
View Related
Jan 22, 2004
Is it possible to make a join between two tables from different databases on same server? if yes, how can we do that?
And also if I want to make a join between tables on different databases on different server?how to do this. Please advise.
View 2 Replies
View Related
May 22, 2008
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies
View Related
Apr 24, 2007
i have 2 db's that are totally different except that they both have a table users and I want to make it that anytime the user table is updated or added to in one db - that it is copied over to the other db.
is this possible?
View 9 Replies
View Related
Mar 26, 2008
Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???
If any server variable is used then please mention that???
thanx in advance
San
View 20 Replies
View Related
Jan 24, 2006
HiI'm working on an ASP project where the clients want to be able toeffectively perform SELECT queries joining tables from two differentdatabases (located on the same SQL-Server).Does this involve creating virtual tables that link to another database, oram I completely on the wrong track?Any hints as to where I might find more information (buzz-words, etc.) wouldbe most appreciated.Thanks
View 10 Replies
View Related
Jan 18, 2007
In a database, I am creating a new db. From there, I am setting up thetables, so that I can eventually create a front end (usually access,but I may attempt to be brave and lose the shell.) Anyway, I want touse a table, read-only for a lookup. It exists in another database onour system. Is there a way for me to link it into this database that Iam working on? I would think of it like in access when you go to do anew table and you choose to link the table from somewhere else. Thatis what I want to do. Can anyone possibly step me through this?I know basics of sql server and like to learn.Thanks!
View 3 Replies
View Related
Apr 10, 2007
(SQL 2005)I'm looking to create a stored procedure to first "select name fromsys.databases where name like '%site'" then pass each name to thefollowing using some kind of loop "USE @name select name fromsys.tables where type = 'U'"I tried a while statement, but the master sys.databases recordsetdoesn't change..It's the loop I can't get to work.Any help is greatly appreciated!
View 3 Replies
View Related
Jul 20, 2005
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
View 3 Replies
View Related
Jul 20, 2005
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
View 2 Replies
View Related
Jul 19, 2007
I want to compare the records in a table on my live database server with the same table that is my test database server. How can I do this?
Note that both databases are mirrors of each other but contain slightly different data and are on different servers.
View 4 Replies
View Related
