Join Two Table From Different Database. URGENT!!
Feb 9, 2006
Hi,
anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:
Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))
Dim sqlCommand2 As New SqlCommand("", SqlConnection1)
Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))
Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)
Sub Page_Load(Source as Object, E as EventArgs)
sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"
sqlconnection1.Open()
............................
The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.
Ann123
View 4 Replies
ADVERTISEMENT
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 4 Replies
View Related
Jan 15, 2008
Hi all,
I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair).
Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currently
there are 5000 fields, with a possibility to grow.
Thanks so much for your suggestions!!
Dilla
View 5 Replies
View Related
Dec 9, 2007
Hi,I have two databases on the same server: COMU0002 and 2007DUand two tables: tecnicos on COMU0002 and telf_cli ON 2007DUBeing on COMU0002 this query doesn't work:SELECT * FROM tecnicosLEFT JOIN 2007DU.dbo.telf_cliON ...The error says: INCORRECT SYNTAX NEAR '2007'Even this query being on 2007DU fails on management studio:SELECT * FROM 2007DU.dbo.telf_clibut SELECT * FROM telf_cli works okAny ideas?Thank you!
View 2 Replies
View Related
Jan 25, 2011
I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method
Here is an example of what i am doing
DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
the above query produces the following result
EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh
How do I join the data coming out from the above query with another table in the database, for example Employee in this case
i.e. somewhat like this (note the following query does not perform the join correctly)
SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID
My desired output after the join would be
EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin
View 8 Replies
View Related
Jan 7, 2015
I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
View 1 Replies
View Related
Jun 2, 2000
HI ,
I am joining two tables
a--3 million records
b--58 million records
i am doing inner join based on common column, but
it is doing cross join, giving 174 million records..
When i saw showplan it is showing
physical---merge join
logical ---inner join.
Could any one pls suggest me..
thank u
--Raj
View 3 Replies
View Related
Nov 2, 2004
i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below
TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM
TOTALESCA shows NuMbers of escalated calls out of totalcalls
table 1
TOTALCALL
AGENTID
DATE
TABLE 2
TOTALESCA
AGENTID
DATE
SAMPLE DATA ON TABLE 1
TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM
SAMPLE DATA ON TABLE 2
TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PM
View 14 Replies
View Related
Sep 28, 2006
I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is Select
*, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status,
(Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title
From
(Select member_id As user_id, member_name, member_tpc_id
From members Where member_tpc_id> 0 And organization_id = '1'
)temp1
cross join
(Select course_id As course_id, course_title As course_title
From course Where course_id IN
(Select course_id From tpc_assignment Where tpc_requirement_id IN
(Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1')
)temp2
Order By member_name, Tpc_TitlePlease help. Thank you.
View 1 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Apr 14, 2015
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
View 2 Replies
View Related
Jul 30, 2006
Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam
View 5 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
May 24, 2007
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Aug 15, 2015
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
View 9 Replies
View Related
Jul 23, 2005
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
View 3 Replies
View Related
Mar 10, 2008
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
View 2 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Aug 27, 2007
hi,
I have a access database. In this database there are 10 tables and this tables are related to each other.
I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
how can I do this?
thanks..
View 1 Replies
View Related
Aug 10, 2007
Hi,
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I am using VB.Net to do the backup and restore.
Is there any way of doing it?
Thanks!
View 21 Replies
View Related
Nov 5, 2004
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
View 12 Replies
View Related
Feb 24, 2006
I have one main Table "MainTable" which I want to relate with "subTable1, subTable2, ..." in such a way that:
"ith subTable" have to be related/joind on "ith row" of the "MainTable", "jth subTable" have to be related/joined on "jth row" of the "MainTable" and so on...
What I want Actually?
I want that when ever I delete a Record in the "MainTable", The corresponding "subTable" have to be deleted Itself.
I thought a solution that, I can cerate a Trigger on Delete of the "MainTable" and it would delete the corresponding "subTable". But I dont know how to ceate that.
Secound solution what I thought is, that may be there is some majic power in the Table Joinings. That I might join "MainTable" row with "subTable" ( ofcourse that I dont know either :))
So my question is, that what is the actual solution for my problem?
What ever solution is please give me a sample also with that. Like in a Trigger how can I write some Expression which can delete the "subTable" for the Currunt delete Row.
Regards,
Omer Kamal
View 3 Replies
View Related
Oct 3, 2001
Is there a way to change the database ID for the databases without deleting them and restoring them again.
I have a source server on which the database order is not same in the target server for trace replays.
I don't want to restore the master from source to the target.
I have 3 databases in order
A ---Db_id 1
B----DB_id 2
C----DB_id 3
i want to change the Db_id of C to 1 instead of 3.
Any help will be appreciated.
View 2 Replies
View Related
Jul 10, 2006
Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.
View 6 Replies
View Related
Mar 30, 2001
Hi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay
View 1 Replies
View Related
Aug 13, 2001
create Procedure AddNewStyle @table_nm varchar (20)
declare @string varchar (5000)
select @string='Update product
Set Product_End_dt = '12/31/05'
from ' + @table_nm + ' c inner join Booktable b
On (c.book_id = b.book_id).............
exec (@string)
--
I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help!
Reddy.
View 2 Replies
View Related
Feb 6, 2001
Hello,
I've a database in Production db which is 300 MB and one of its table has
948900 rows then how can I know how much size this particular table is of.
Thanks.
View 2 Replies
View Related
Feb 2, 2001
Someone, I don't know who, deleted an important table in my database. I've only been able to find restore routines on full databases. Is there any way to restore just a single table?
View 3 Replies
View Related
Oct 3, 2000
Hi,
How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..
View 1 Replies
View Related
Aug 6, 2007
Hi All
I want to convert table structure into xml in the following format.
Eg: <TABLE_NAME>
<COLUMN_NAME>
<IS_NULLABLE> </IS_NULLABLE>
<CONSTRAINT_TYPE> </CONSTRAINT_TYPE>
<CONSTRAINT_NAME> </CONSTRAINT_NAME>
<TABLE_CATALOG> </TABLE_CATALOG>
<LENGTH> </LENGTH>
</COLUMN_NAME>
</TABLE_NAME>
where TABLE_NAME is the table name in database and COLUMN_NAME is name of the column in above table and the remaning are the attributes to that column.
please suggesst me the sql query to get the following structre of any table in database,
Thanks
Rupal
View 1 Replies
View Related
Sep 17, 2006
Hi .... This is my problem .....
I have two table with the same structure (ID, DATE_1, DATE_2).... PLANS and AUD_PLANS. I need to create a trigger (FOR INSERT) that do the following things:
In PLAN table i have the next row: (1234, '01/01/2006', '30/01/2006'). I need to capture these values to compare with the row to insert. How can i do this?
I try this into de Trigger Code ....
DECLARE @FECHA_1_ANT AS datetime
DECLARE @FECHA_2_ANT AS datetime
DECLARE @FECHA_1_INS AS datetime
DECLARE @FECHA_2_INS AS datetime
Select @FECHA_1_ANT = P.DATE_1, @FECHA_2_ANT =P.DATE_2 from PLANS P inner join INSERTED I on P.ID = I.ID
But the values for @FECHA_1_ANT and @FECHA_2_ANT are the same that the values to insert.....
Please help me !!!!!
Thanks
View 3 Replies
View Related
