Hi;
I am looking for suggestions about how to solve a problem using tsql.
I have been asked to create a report concerning 4 tables.
Each of the 4 tables is in its own database.
The 4 tables are identical in name and structure.
I would like to query all 4 of these tables and get the results as if
they were one table.
All suggestions welcome, thanks.
Steve
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.
Hey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)
Hello,
This is probably a simple solution but I'm a bit confused about which flows to use.
I have two databases on different servers that contain the same information but for the appropriate server. I want to retrieve data from each of the servers using a "select" statement.
Would I use a ForLoop and change the connection in a Script Task and then execute the SQL statement? Or can a ForEach Loop be used, but I'm not sure which type of enumerator.
Thanks for any help you can give.
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 RelatedHi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies View RelatedHi all,
lets say i have a table as follows
id name1 red2 orange3 green
and this has s join to another table via the id
table2
id event 1 stop2 stop3 start
now if i make a view and drop these two tables the jkoin is there but i would liek to report on all names who have a event of "go"but it gives me no results , which is right . but how can i output all the names and in the evnet put a empty SO
name eventred emptyorange emptygreen empty
thanksrobby
I am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish.
Table1 = TVs
ID/SKU_ID/Manufacturer
1 87654 Samsung
Table2 = DVD_Players
ID/SKU_ID/Manufacturer
2 765234 Kenwood
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
Hi,Any suggestions for a good intorudction to OLAP querying with MDX?Thanks,Ron.--Performance Intelligence, Inc.Spy 4 DB2 - http://www.pireporting.com/spy4db2.html
View 1 Replies View RelatedHi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
Hi,
I need to merge tables from several databases. Before the merge the table contents must be checked to rule out any duplicates.
Is there a most efficient way to do it? Thanks to you all.
I have a query that involves tables from 2 different databases. Using the query analyzer I can construct my query and it works great. Now I am trying to implement the query into my code and I am confused about I handle the connection string(s) since I am now using 2 different databases.
Can anybody help?
Cheers,
AzF
Hello All,
I have multiple SQL Server 2000 databases all identical in structure (Company1.mdf, Company2.mdf, Company3.mdf). I have a situation where while connected to Company1, i want to create a record in Table1 and have that same record automatically be created within Company2 and Company3.
If i was working with MSAccess, i could use linked tables where i would link Company2 and Company3 to Company1 Table1. This way if any changes are made, all three companies and working from the same record set.
My situation deals with mutiple tables that need to be in line with each other across mutliple databases.
Your suggestions are appreciated.
Thanks
I'm trying import 7 tables from each of 30 SQL2005 databases into a SQL2005 Consolidation database. I can simply create data flow tasks for each one but instead I would like loop through a list instead.
I've created a table to house the names of the databases from which I want to import the data.
I've created SQL task to return the database names from the table as a "Full Result Set".
I've assigned the result set to a user variable (type = Object) an named the result name 0
What I'd like to do is create a data flow task which connects to each of the databases and imports 7 specified tables from each database appending the table name with my database name in the result set.
I'm stuck on how I'd set the connection strings in my OLE DB Source in my Data Flow task. Any insight would be greatly appreciated.
Thanks in advance.
Bill Webster
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
Hi,I'm having two databases in the name of n1,n2 having same designI want to query both the databases by giving the database name in queryDatabasesN1N2Table(both database contains same table)DetailTable detailsNo -intName -varchar(10)Now i want to check the detail table in both n1 and n2 using the selectqueryFor that I have written query it is not workingselect * from N1.DetailSelect * from N2.DetailThe above query is not working in query analyser,I'm getting error as"Invalid object name"In query analyser the Northwind database is currently selected, anywayI have given the database name it has to query from N1 database. How touse a select query with database name?Mani
View 1 Replies View RelatedHi,I need to run a query that gets an ID from one database table and then matches it to another table in a different database, is a query of this type possible if so how?Thanks,drazic19
View 2 Replies View RelatedMy query pulls data from two separate databases, and it runs extremely slow. The queries run well when they are pulling from their own database. When I join them to pull from both databases.
View 9 Replies View RelatedI know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA
View 1 Replies View RelatedMy end result will be a query with about six parameters total. But for right now I am trying to one by one for testing purposes. This is my first SQl project. All of my information goes into an Access DB. I am basically creating a search page with parameters for the employees to use to search for data. I need the query to work if all parameters, or combinations of parameters, or just one parameter is chosen. I started with one and got to three but now I'm stuck. My first two parameters are dates, so the data can be chosen by date range, and the second is location id. If I choose date range by itself it works, If I choose location id by itself it works, but If I choose both it doesn't work. Instead of filtering it gives me everything and I have a statement for it. Can someone please help me and tell me what am I not doing right. Here is my code;
SELECT *
FROM ASFdata
WHERE
(ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#"> AND <cfqueryparam value="#Form.datefield#"> <> datefield AND <cfqueryparam value="#Form.datefield2#"> <> datefield) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND NOT <cfqueryparam value="#Form.reportlocid#"> = reportlocid ) OR
(ASFdata.datefield BETWEEN <cfqueryparam value="#Form.datefield#"> AND <cfqueryparam value="#Form.datefield2#"> AND ASFdata.reportlocid = <cfqueryparam value="#Form.reportlocid#">)
I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:
DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92
DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92
Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:
Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190
or
Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190
Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%
I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:
= count((Fields!sale_number.Value,"DS2"))
This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.
If I try something fancier such as:
=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))
The report won't even run.
I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).
I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.
Thank you,
cmk8895
OK looking for anyone out there that may have already tackled this and what they may have learned
I have multiple flat files providing dimensional data to multiple tables. The source files and destination tables are very similiar in structure, the columns are named differently in each table
ID - surrogate key
Code - match for lookup
Desc - updated column
flow options I have considered or might work are:
source (flat file) - this is easy to make dynamic
lookup against existing Table -
1. I can make this dynamic on the advanced tab by restricting memory. have not really tried this option yet. Seems like it might be a performance issue for large tables but so far it seems all these dimensional tables will be small.
2. Could create a view in the control flow,before execution of the DataFlow with generic column names against a variable table and use this same view for every lookup - again not sure of performance etc
3. could just replace this with a merge/join as long as it does full outer, have not used this option so I'm not quite clear on the dynamic properties
now the last part update or insert
Insert
1. Seems like I could use the OLEDB destination and make the table a variable - have not tried to see if this works.
Update-
1. looking at the OLEDB command but not sure how it would handle a variable table name in an update statement - will need to try this out
2. OLEDB command that calls a proc that executes a dynamically built update statement
3. Write the update items to a generic holding table then run an update in the control flow from a dymanically built sql statement.
Hi all. I have the problem on query.
This is my query.
select D.fullname, P.religion, E.empno from pspersonaldata as P
inner join hremployees as E on P.dcno = E.empdcno
inner join psdatacenter as D on D.dcno = E.empdcno
where P.religion in ('Born Again','Baptist', 'Catholic')
How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed.
If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist.
Thanks
-Ron-
Hi,
I want to perform a join on two tables from two separate database servers.
Code Block
SELECT *
FROM tbl1 AS t1
INNER JOIN
"SERVERASQL2000".Production.tbl2 AS t2
ON t2.UserID = t1.UserID
Why do I get this error...
Invalid object name 'SERVERASQL2000.Production.tbl2'
thanks.
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
View 1 Replies View RelatedFolks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve
View 2 Replies View RelatedHi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.
Please email me at dickson.lau@rogers.com
Thanks again.
Dickson
I get a 90-120 second blocking when send 15 or so simultaneous queriesto SQL Server 2000 that query a view made up of two joined tables.After each query is blocking for the same amount of time they allreturn. Further identical queries of this type work in 3-4 seconds(caching?) until hours later where it happens again. If I query thetables directly (without the view) I still get the same blocking. If Iremove the join (it is a simple inner join on two columns) I do not getthe blocking.Any ideas?
View 3 Replies View RelatedThis seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
View 2 Replies View RelatedHi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?