T-SQL (SS2K8) :: Get Difference Between Data In Two Tables
May 27, 2014
I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:
CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,
I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.
I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT) select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID from SALES_DETAIL, CUSTOMER where SALES_HEADER.sales_id = CUSTOMER.cust_id group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
I've table and data as follow, declare @tAccount table ( isLocked bit, LastLockoutDate datetime, currentDte dateTime )
insert into @tAccount values('true','2014-04-26 16:11:25.337',getdate()) Let's say, current data as follow, isLocked| LastLockoutDate| currentDte 12014-04-26 16:11:25.3372014-04-27 01:45:15.053
How to get different in minutes between currentDate and LastLockoutDate? Mean, currentDate - LastLockoutDate.
Hi im trying to find the difference between 2 silmilar tables in sql 2000. 1 table has 279,042 records and the other has 279,003 records, I ideally want to difference so to enable me to merge the two tables I would really appreciate any help
Hi,We have 2 tables as follows,TableA------------doc_id ver_id ref_dateTableB------------doc_id ver_id ref_min_dateWe need to find out the records that exists in TableA but not in TableBand also to get the minimum of TableA.ref_date from the result.For example,TableA has the following rowsdoc_id ver_id ref_date100 1 10-AUG-2006100 2 12-AUG-2006100 2 13-AUG-2006100 2 14-AUG-2006100 2 15-AUG-2006200 1 17-AUG-2006300 1 18-AUG-2006TableB has the following rowsdoc_id ver_id ref_date100 1 10-AUG-2006200 1 10-AUG-2006Result should be as followsdoc_id ver_id ref_date100 2 12-AUG-2006300 1 18-AUG-2006Please help us in writing the queryThanksAshok
Hi, First post here :) Subject might not make sense, so allow me to explain. I have a table, tbl_finalized. A unique record consists of an emp_id, building, and school_year. I have another table, (call it table2) where emp_id, building, and school_year are fields as well. What I'm trying to do is... grab all the emp_id's from table 2 ( along with the school_year and building ) and then see if it exists in tbl_finalized. If it does, I don't want it. I want all the usernames in table2 that don't exist in tbl_finalized.
Here's an example: table2 contains the following record: emp_id: xxx, building: 333, school_year: 2008 emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
tbl_finalized has the following records: emp_id: yyy, building: 333, school_year: 2008 emp_id: xxx, building: 333, school_year: 2007 emp_id: xxx, building: 333, school_year: 2008
The result I desire is: emp_id: zzz, building: 333, school_year: 2008 emp_id: yyy, building: 444, school_year: 2008
I hope this makes sense. If not, please ask and I will do my best to explain. I have light understanding of joins, but not sure how to get the difference like I desire in this case... TIA.
I receive the following result set from TableA (In Time)
7/9/2013 9:27:00.000 AM 7/9/2013 10:24:00.000 AM 7/9/2013 11:25:00.000 AM 7/9/2013 1:23:00.000 PM 7/10/2013 7:27:00.000 AM
Then we receive the following result from TableB (Out Time)
7/9/2013 9:30:00.000 AM 7/9/2013 10:29:00.000 AM 7/9/2013 1:37:00.000 PM [NULL] [NULL]
We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.
I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.
and out_time = (select min (out_time) FROM tableB WHERE tableB.record# = tableA.record# and tableB.loc_id = tableA.loc_id
GROUP BY tableB.record#, tableB.loc_id )
It seems I need to focus on the minimum datediff for each record line but can't figure that part out.
Hello Tsql experts, Can you please explain to me what the differnce is between selecting into a table and creating a new tabel and inerting in to it like so:
First way: select names into #ancestors from names
and
second way
create table #names( name varchar(10) )
insert into #names (name) values(name) select names from names
is it just personal choice or performance in one method is better than the other.Can you please explain. Thanks
Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
I want to compare two tables and log the difference in new table with the fields as (old value,new value, column name). The column name should be the changes value column.
for rows having no start and endtime assume it as regular intervals.
So i need to show available appointment with duration one hour with the available schedule which is for every five minutes
Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it
For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.
DECLARE @num int=5 ,@LASTtime TIME =CAST('23:55' as TIME) ,@Time TIME =CAST('00:00' as TIME) ,@Timeprev TIME =CAST('00:00' as TIME) WHILE ( @Time<>@LASTtime) BEGIN
I need to search all tables in my user database to find a value. I don't know what the column name is.
I know of one table that contains the value, but I need to look through all the tables and all there columns to see if I can find if this value exists in more than one place. It is an int value - 394789.
I am having trouble with joining 3 tables.1 table is the primary call it jobs, second is material, third is called labor...job will have all jobs, SOMETIMES the job will have material, sometimes job will have ONLY labor, BUT most of the time it will have job, material and labor...
I've having difficulty joining two tables and getting the results I want. What I'm trying to accomplish is an Health Savings Account (HSA) upload file to go to our Insurance vendor. Here's a summary
Table1: PS_AL_CHK_DED
Description: This table stores all deductions in an employee's paycheck. I'm specifically looking for a field in that table called AL_DEDCD where it equals "H". This is the deduction from the employee's paycheck.
Description: This table stores the employer contribution based on their deduction. For example, we match 50% of their contribution. It is possible for the employee to have a deduction (stored in the ps_al_chk_ded table) but it is not possible for them to have an employer contribution (stored in ps_al_chk_memo) without a deduction (stored in the ps_al_chk_ded).
What I want in my output is a combination of these tables that looks at the employee in the ps_al_chk_ded table, takes only the records where al_dedcd = H and returns the corresponding employer contribution (memo_cd =H in al_chk_memo). So you almost have to join the two tables on all three of those fields and I can't get it to work. Here is what I would like the data to look like:
This didn't seem like it was too complicated but no matter how I do it, I can never get the appropriate amount from the employer contribution. I attached a screenshot that shows my queries and result set. In the screenshot, it returned the employee deduction just fine but then returns 48.25 for the employer contribution. However, the MEMO_CD in that specific record is equal to T. In this case, it should return 0.00 for the employer contribution. If I try and add something like d.al_dedcd = c.memo_cd on the join, I barely get any results. The key is matching on those fields and then returning 0.00 when there is no record of MEMO_CD = H in the al_chk_memo table for the deduction in ps_al_chk_memo.
I need someone to give me a small briefieng on how a enterprise data warehouse (EDW)differs from a regular database? Currently we have an appplication that accesses a database with about 18 tables. We also have a Data Warehouse. For some reason I was thinking that it would be possibly to migrate thedatabase into the data warehouse. The reason is that in looking at the schematic design forthe data warehouse there are some data tables that could also be used by our application that uses the DB. I guess I am confused because I am not sure if a data warehouse is used in the same wayas a database?
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
RID, RType, GID 001, m, g01 002, m, g01 002, m, g02 002, m, g03 003, m, g01 003, m, g03 a, T, g01 a, T, g02 a, T, g03 b, T, g02 b, T, g03 b, T, g04
4. Group
GID g01 g02 g03 g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
I have task where i need to update the 2 columns from 2 different tables. I need to get one column from one table and update to the other table.If the column name do not match....
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.
'test1', 'need this record', 25, {d '1901-01-01'} 'test3', 'some longer value', 23, {d '1900-01-01'} declare @table1 table ( col1 varchar(10) not null, col2 varchar(200) null, col3 int not null,
