Trying to compare unique fields from two databases.
Basically what i'm trying to do is get all of the id's out of one field that don't exsist in the 2nd database.
so I have this query
Code:
select Distinct Id
from table1
where ID!= (select Distinct ID from table2)
I'm getting this error
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Now I don't understand why because then I query
select id from tables 2
I only get 1 value for each row.
Now you should know, the two columns in either table are different, in one of them the first value is "1" in the other the first value is "00000001" although I don't think that should effect anything.
I have a Development and Production database and they are a little out of sink. Does any one know of a way to compare two databases (tables, fields, sp) and report the differences? Thanks in advance.
i have two databases best and books.Most of the tables present in best are in books except some. i have been given a task to list out the tables and columns of which are present in best database and not in books database.
Pls suggest me the simplest and quickest way to do this. this is very urgent.
Hi,I would like to compare two sql server databases.I need to find the difference in the two database(i.e table, colums,data type)Is there any free utility available to compare sql server databaseI'm using sql server 2000.Help me out to compare databasesThanks & Regards,Mani
Hello,I would like to make a comparison between two databases in SQL server,to find differences between tables, procedures and so on.I have searched the web and found several softwares able to perform thetask, but none able to compare a database with a backup otained from aSQL Server database, or between two backups.Is this possible ? Is there a software able to perform such comparison?Thank you for your help.--L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.[Jean Yanne]
Im busy working on converting one DB to look like another one and I am using a data comparison tool to generate a script that I can hopefully use to upgrade an existing DB.
The problem is occuring with only one table and as soon as the script is run I receive the following error msg in SQL server 2005; ______________________________________________________________________________________________________ 'Patient' table - Unable to modify table. Cannot insert the value NULL into column 'ClinicCode', table 'DCS.dbo.Tmp_Patient'; column does not allow nulls. INSERT fails. The statement has been terminated. ______________________________________________________________________________________________________
Now the problem I am facing and I am hoping you could help with is where I can find the .Tmp table. This is obviously a temporary table created, but I have no clue as to how to alter the values of that temporary table in order to facilitate the changing of the table to coincide with the updated table.
Some help would be appreciated in this regard.
p.s. Hopefully I explained the problem sufficiently for you guys to help out!
Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0), with same exact schema. If possible, how could we do it?
Hi, does anybody know a nice tool to automatically compare and showonly the structural differences between two databases, this is, thedifferences in the columns of a table, constrains, data type, etc.ThanksJ.A.
I wan't to compare two SQL Server databases. i.e., I wan't to check both databases structure and add if necessary.
In my project I am using SQL Express database and now we update some fields in the database. So, if we wan't to update the new versions for those customers who are using old database we need to update the database also. It is not recommanded that to manually add the fields in the database and is time taking too. So, I wan't to compare two databases(old and new) and update the fields in the old database.
Any confusion in my question plz let me know so that I can be more clear.
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.
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Hi, I have a table with 3 columns inside - record_id (int) - user_id (varchar) - login_date (date) it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use SELECT COUNT(*) FROM table1 WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to - How many people visit the site only once? - How many people visit the site only twice? - How many people visit the site three time?
I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?
<< The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. >>
ok ... it was my error mess, but i just wrote this code :
*********************************************************** Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim strSelect As String conPubs = New SqlConnection ("Server=localhost;uid=ggggg;pwd=uuuu;database=eeee") strSelect = "Select username From userpass Where username = '" & usernametxt.Text & "' And password = '" & passtxt.Text & "'" cmdSelect = New SqlCommand( strSelect, conPubs ) conPubs.Open()
if Not(cmdSelect.ExecuteScalar() IS Nothing) then 'findeduser.text response.redirect("welcome.aspx") conPubs.Close() else msg.text="ID or Pass is incorrect!!" conPubs.Close() end if ***********************************************************
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id. Can anyone help?
Hi All, I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format: 5/23/2006 2:27:45 AM I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me. SELECT id, datestampFROM table WHERE datestamp > DateAdd(d, 28, GetDate()) Thanks in advance, Jake
Hi , I have three tables T1 , T2 AND T3. T3 is having fields as a combination of T1 and T2 fields.How can I compare T1 and T2 field values with T3 FIELD VALUES.
How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-
6/6/2001 7:23:42 PM 6/6/2001 8:01:08 PM and so on....
I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.
Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.
I'm new to SQL Server so if the following sounds stupid then apologies. I am trying to design a query which compares columns and filters according to the result of the comparison. We are a UK based charity that provides financial help to families with disabled children (www.familyfund.org.uk). We have a large database (250,000 entries) which we know contains some duplicate/split files from a recent migration. We need to identify these files but not delete them. Currently I am using the following:
SELECT dbo.Families.famId, dbo.Address.street, dbo.Children.childId, dbo.Address.postcode FROM dbo.Children INNER JOIN dbo.Families ON dbo.Children.family_no = dbo.Families.famId INNER JOIN dbo.Persons ON dbo.Families.famId = dbo.Persons.famId INNER JOIN dbo.Address ON dbo.Persons.addressId = dbo.Address.addressId WHERE (NOT (dbo.Children.eligStatus IN (3, 4))) GROUP BY dbo.Children.childId, dbo.Address.postcode, dbo.Families.famId, dbo.Address.street HAVING (dbo.Address.street IS NOT NULL) ORDER BY dbo.Address.street
Obviously this returns all 250,000 records and then we have to search manually. We would like to run a query which compares families.famID to address.street so that where famId has more than one address attched it is returned to the results grid. Does this make sense? is it possible? Any help would be gratefully received
select * from independent_investment_certificate_temp
where independent_investment_certificate_temp.ticker_key = 31
and convert(varchar(40), cast(independent_investment_certificate_temp.maturity_date AS datetime),107) > convert(varchar(40), cast('2006-12-10 10:29:50.733' AS datetime),107)
independent_investment_certificate_temp.maturity_date value is '2005-01-21 00:00:00.000' the query above returns a value even though '2005-01-21 00:00:00.000' is clearly < '2006-12-10 10:29:50.733' but why can anyone plz explain.. how can i compare dates stripping tyhem of the time so that all dates on the same day will be equivalent regardless of the time
Hi, i got this query below, which I'd like to use to compare two sets of date...
select count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where chtype = '1' AND convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)
the format of the stdate and dateEntered (a datetime datatype) looks like - 25 Aug 2005
right now, the query simply uses the first 2 letters of the date to fetch the record. That would not do the job as i want the thing to compare the whole lot! not just the 1st two letters...
I have 2 seperate databases and I need to check for rows that are different from each other in a table.
I used access to link the tables in a database and am using queries to check the tables. However, I am having trouble formulating the SQL. What I want to do is not just check for the ID field to see if it exists, but to make sure the whole row exists. How can I form an SQL statement for this?
I tried something like:
Select * from table1 where Column1 NOT IN (Select Column1 from Table2) AND Column2 NOT IN (Select Column2 from Table2). However, I do not think this is correct. I want to make sure that the rows are compared, not individual values.
I will try to explain my problem as best as possible without going into too much detail.
Basically I have two tables, AgentSchedule AgentPunchData
These tables both contain agent names.
The schedule table has a column called "SHEND" which is a DATETIME datatype which identifies when an agent shift should end.
The entries all have a date attached which means nothing, for example "12/30/1899 9:00:00 PM"
The PunchData table has a column called "LOGOUT" which is also a DATETIME, the entries will have the real date and look something like this: "3/28/2008 9:23:00 PM"
I need to strip the date out of these functions so I can compare the times. I need to query the databases and return any instances where the LOGOUT time is > the shift end time (SHEND).
So far nothing I've tried has been successful. I'm completely stumped, I've tried so many different things and nothing compares the two times correctly. SQL DATETIME is the biggest pain in the ass, I wish we had SQL 2008 so I could just use TIME.
