Update Query Joining Tables From Separate Databases
Apr 17, 2008
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
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
Hello,I am a beginner with SSIS. I have a scenario ...
I have source DB 'MySource' and a destination DB 'MyDestination' I have created a SSIS to push data from my source data base to detination DB.
I have a scenario where in i have to push data from the source DB to the Destination DB , depending on a join condition for which the data is in Destination.
I would not be able to acces both the DBs while writing query to pick data from .. source DB. How do iproceed ..
let me give an exmple
Source DB:
Table1
---col1 --col2 --col3 -- -- --coln
Destination DB;
Table2: --col1 --col2 --col3 -- -- -- col n
Table 3:
--col 1 --col 2 -- col 3
Now i Have to move data to table 3 into destination DB getting all the records which pass a join on table1 and table2 on cloumns 1 ,2 and 3 ..
I have only read access on the the Source DB.. pls let mw know how i can solve this scenario in SSIS.Thanks and Regards,Raja Tirumala Rao
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username 2 FROM users 3 WHERE user_username LIKE '%[!-)]%';
I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:
Code:
$rows = ff_select( "select ". "u.email as email, ". "c.user_id as user_id, ". "u.name as name, ". "r.age as age ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". "left join #__rnr_contest as r on c.user_id = r.userid ". "where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ". "order by email"
anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?
Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' andLOCSubClassHave.LCNT = '0')This query seems to be ignoring the 'and' part of the clause.Basically I want select from table1, table2 where LCNT in table1 is not0 andLCNT in table2 is 0.I have verified the LCNT's numbersThis query returns 2 columns with 1700 rowsIt needs to only find a few rows.What am I missing, any ideas, thanks for any help.
Two tables:T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 isthe primary key.T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factornumeric(18,2))--x1 is the primary key. T2 contains non-overlappingvalues. So for eg., a few rows in T2 may look like.1, 51, 51.999, 512, 52, 52.999, 52........32, 82, 82.999, 82........T2 is basically a lookup table. There is no relationship between thetwo tables T1 and T2. However, if the TestVal from T1 falls in therange between FromVal and ToVal in T2, then I want to updateResultFactor in T1 with the corresponding value of Factor from the T2table.------Example for illustration only---------------Even though tables cannot be joined using keys, the above problem is avery common one in our everyday life. For example T1 could beemployees PayRaise table, c1=EmployeeID, with "TestVal" representingtest scores (from 1 to 100). T2 representing lookup of the ranges,with "Factor" representing percent raise to be given to the employee.If TestVal is 65 (employee scored 65% in a test), and a row in T2(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 intable T1 from T2 using sql;. Basically T2 (like a global table)applies to all the employees, so EmpID cannot serve as a key in T2.---------------------------------------------------------Could anyone suggest how I would solve MY PROBLEM using sql? I wouldlike to avoid cursors and loops.Reply appreciated.Thanks
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum FROM TableA AS A INNER JOIN TableB AS B ON A.col1 = B.col1 INNER JOIN TableC AS C ON B.col2 = c.col2 WHERE A.col3 = constant 1 AND B.col4 = constant2 GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B. Â Â When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp;  When Join C, nbsp;When A Join B, nbsp;  When Join C, nbsp;When A Join B, nbsp;   When Join C, "line-height:13.5px;">
Q2:Â The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
I need to do a inner join on tables from two separate databases. I understand that you can do this by using this type of syntax: select a.col1, b.col2from db1.dbo.tab1 a, db2.dbo.tab2 bwhere a.col1 = b.col2however, how do I reference the two databases in the following code? Thanks, Tim
Function GetConnectionString() As StringDim ConnectionString As String = ConfigurationManager.ConnectionStrings("MainWeb").ConnectionString Return ConnectionString End Function Using conn As New SqlConnection(GetConnectionString()) conn.Open() Dim sql As String sql = "SELECT CaskInfo.CaskID, CoCInfo.CoCName, AmendmentInfo.AmendmentName FROM CaskInfo INNER JOIN CoCInfo ON CoCInfo.CoCID = CaskInfo.CoC INNER JOIN AmendmentInfo ON AmendmentInfo.AmendmentID = CaskInfo.Amendment WHERE "For i = 0 To UBound(words) If i > 0 Then sql = sql + " OR " sql = sql + "(CoCInfo.CoCName + ' ' + AmendmentInfo.AmendmentName) LIKE '%" + words(i) + "%'" Next ' lblResults.text = sql' Exit Sub Dim com As SqlCommand = New SqlCommand(sql, conn) Dim result As SqlDataReader = com.ExecuteReader() Dim SearchResults As StringWhile result.Read() SearchResults = SearchResults + result.GetInt32(0).ToString + " " + result.GetString(1) + " " + result.GetString(2) + "<br>" End While result.Close() lblResults.text = SearchResults conn.Close() End Using
I have two databases with multiple tables. Same tables same fileds.Both databases contain records, but they should not match each other.(I will run a report for matches before this and delete any data thatis not current to ensure this is the case)How do I go about moving data from one of the databases to the other inorder to create a single database with all the data? Unfortunately mySQL is limited and I cannot image how I would work around the uniqueids. Append or update maybe?Thanks in advanceAndrew
I would like to write a query that will join two tables in separate databases (same server). Then update a field in one table using data and criteria from the other.
Its easy to do this in Microsoft Access by linking the tables using ODBC, but I found its not so simple to do directly in SQL Server
Hey all, I want to run a query that returns the count of records returned by two other queries. Having much trouble with this... I'm sure it's just a triviality. Thanks in advance...
I have an existing database with approx 500,000 rows and accessed by afew hundred users per day creating approx 1,000 new records per dayplus typical reporting - relatively low volume stuff for SQL Server.I'm about to add a process that will be importing data daily fromlegacy databases and summarizing it for reporting purposes, integratingit with the existing database. This volume of data will be considerablyhigher, perhaps 100,000+ rows per day, which will be deleted once ithas been summarized and the results written to some intermediatetables.Is there any concern about mixing different levels of volume within onedatabase? As I'll be creating lots of rows daily and then deleting themI was wondering about fragmentation, transaction logging etc. andwhether having this processing in a separate database from the mainapplication would be 'better'.
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
I am looking for some assistance with an update query that needs to link 3tables:This query ran and reported over 230,000 records affected but did not changethe field I wanted changed, not sure what it did.I did notice that the "name" in "GM_NAMES.name" was colored blue in QueryAnalyzer. Is it bad to name a column "name"?UPDATE ABSENCESset CustomerContactID = cicntp.cnt_idfrom absences, cicntpwhere (SELECT cicntp.cnt_l_nameFROM cicntp INNER JOINgm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOINAbsences ON cicntp.cnt_id = Absences.CustomerContactID)Next I tried this query which is still running after 75 minutes (on alaptop)update absencesset CustomerContactID = cicntp.cnt_idfrom absences, cicntp, gm_nameswhere gm_names.name= cicntp.cnt_l_nameAs you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.Absences.CustomerContactID is what I need updated, when finished it shouldmatch CICNTP.cnt_idGM_NAMES is a temp table and matches records in CICNTP.cnt_l_nameCan some of you school this newbie on the best way to do this?Thanks a bunch!
I am new to T-SQL and triggers Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert AS Update customer SET cust_id =( SELECT Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id)))) + Convert (varchar(10),i.id) + Substring(i.lastname,1,3) + Substring(i.firstname,1,1) from employee C INNER JOIN inserted i on i.id=c.id) from employee C INNER JOIN inserted i on i.id=c.id
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.
My scripts are as follows: Main View SELECT CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, CUSTOMER_POLICY_DETAILS.POLICYNUMBER, --TotalPayable IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalPayable, --NetPremium IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS NetPremium, --IPT IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS IPT, --Fee IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS Fee, --TotalCommission IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalCommission
FROM ACCOUNTS_CLIENT_TRAN_LINK INNER JOIN ACCOUNTS_TRANSACTION ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID INNER JOIN ACCOUNTS_TRAN_BREAKDOWN ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID INNER JOIN LIST_TRAN_BREAKDOWN_TYPE ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID INNER JOIN CUSTOMER_POLICY_DETAILS ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID INNER JOIN SYSTEM_INSURER ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID INNER JOIN SYSTEM_SCHEME_NAME ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID INNER JOIN CUSTOMER_INSURED_PARTY ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID WHERE CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'
GROUP BY CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, ACCOUNTS_TRANSACTION.Transaction_ID, CUSTOMER_POLICY_DETAILS.POLICYNUMBER
Add on View 1 CREATE VIEW TOPCARDPA AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'
Add on View 2 CREATE VIEW TOPCARDRESC AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'
Join Result Script SELECT TOPCARD.AUTHORISATIONUSER, TOPCARD.AUTHORISATIONDATE, TOPCARD.TRANSACTION_CODE_ID, TOPCARD.SURNAME, TOPCARD.INSURER_DEBUG, TOPCARD.SCHEMENAME, TOPCARD.POLICYNUMBER, TOPCARD.TotalPayable, TOPCARD.NetPremium, TOPCARD.IPT, TOPCARD.Fee, TOPCARD.TotalCommission, TOPCARDPA.SELECTED, TOPCARDRESC.SELECTED FROM dbo.TOPCARD TOPCARD INNER JOIN dbo.TOPCARDPA TOPCARDPA ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID INNER JOIN dbo.TOPCARDRESC TOPCARDRESC ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID
I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
Hi. I'm new to SQL, and need to join 2 tables... any hints??? table1:id (int)title(varchar(50))body(text) table2:id (int)title(varchar(50))body(text) somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information: table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" I would like to get... id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body" Does anyone know how to get this? I am fairly flexible if i need to change things... cheers, eh!
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
Hello all, I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do??????? Mahmoudona
