|
[Product plug]
We use Red-Gate SQL data compare for this job and it is excellent but a bit expensive for a single requirement.
[/Product plug]
Do an inner join between the 2 tables using EVERY column, this will identify the identical records, then select from the(s) where the Ids are not in the inner join.
Inspect this result and discard the joins that are not relevant and repeat.
When you have the critical records decide what you need to do to merge them.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know you are trying to run a query to find the different rows. Would using external tool acceptable? If so, look at SQLDBDiff [^]which is freeware and does decent job comparing DB and more.
|
|
|
|
|
Use FULL OUTER JOIN.
CREATE TABLE #first
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);
CREATE TABLE #second
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);
INSERT INTO #first (Id, Name) VALUES (1, 'One');
INSERT INTO #first (Id, Name) VALUES (2, 'Two');
INSERT INTO #first (Id, Name) VALUES (3, 'Three');
INSERT INTO #first (Id, Name) VALUES (9, 'Nine');
INSERT INTO #second (Id, Name) VALUES (2, 'Two');
INSERT INTO #second (Id, Name) VALUES (3, 'Three');
INSERT INTO #second (Id, Name) VALUES (5, 'Five');
INSERT INTO #second (Id, Name) VALUES (7, 'Seven');
SELECT f.Id AS fid,
f.Name AS fname,
s.Id AS [sid],
s.Name AS sname
FROM #first f
FULL OUTER JOIN #second s
ON s.Id = f.Id
WHERE s.Id IS NULL OR f.Id IS NULL
DROP TABLE #first;
DROP TABLE #second;
|
|
|
|
|
Hi folks,
I have a requirement to exclude weekends in stored proc: Please find the details below:
---------------------
Declare @Days int
Declare @DaysLatest int
select @Days = sum(datediff(day, CONVERT(DATETIME,isnull(StartTime, getdate()),101), CONVERT(DATETIME,isnull(StopTime, getdate()),101))) from dbo.Timers where isnull(IsProgress , 0) <> 1 and IdNumber = @IdReport and Status = @StsID
set @DaysLatest = datediff(day, CONVERT(DATETIME,ChangedDt,101), getdate()) - @Days
--------------------
StartTime & StopTime are 2 Datetime columns in a table... As mentioned above, i need to exclude weekends when i sum up the dates (StartTime & StopTime) to @Days (The total should not count the weekends) variable AND at days difference (ChangedDt, @Days) to @DaysLatest (The result should not include the weekends count)...
This is my requirement... pls let me know if it is unclear... pls help me frenz... tks in advance...
|
|
|
|
|
See Answer[^] given in that thread may help you towards.
Regards,
Hiren.
be good(Help people in CP),do good(Vote if one finds helpful) all will happen good, In case happens bad(You are getting downvote for your best try to help OP) it will be good for later after(Countered with more points by humble member). - Gita sar in context of CP.
-So Guys don't care about downvote believe in you.
|
|
|
|
|
There is a dayofweek (in sql server) that can be used to filter out the weekend days, generally 1 and 7. Explore the date processing functions in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi frenz, tks for ur prompt response... well i have seen this but didnt knw how to implement for my requirement... could any of u pls help me on hw can i change my sp to meet my requirement? tks in advance..
|
|
|
|
|
frenz,
pls help me out of this issue... i tried using DATENAME(WEEKDAY) but no luck on how to use it properly... i cannot use Date of the week since it all depends on the server datatime settings and i dont want to be dependent on that... pls help me guys...
my requirement is to calculate the no. of days between two specific dates excluding weekends... tks!
|
|
|
|
|
Dear All,
simply i have a combobox in a form (am using Access 2007), and the data source for this combobox is a table of two feild id and firstname. (firstname is choosen to load in the combo)
when a selection (combo_Change())of a name is done, a msgbox will show the name. but it rather showing the corresponding id value.
ie: if i selected Mark form the list, a msgbox will get me 7 , which is his id in the table.
i used: me.combobox1.value , and all othe properties not working, like: .selected(i), itemdata(i) where i is the selected idex.
What am i missing here????
regards,
0 will always beats the 1.
|
|
|
|
|
Did you try me.combobox1.text!
|
|
|
|
|
me.combobox1.text giving he following error:
You cannot reference a property or method for a control unless the control has the focus
regards,
0 will always beats the 1.
|
|
|
|
|
Hello All,
Need to move certain tables, not all tables from one database to another database, all the data and its properties. This is in SQL 2005?
Any pointers?
Thanks!
|
|
|
|
|
you have many choices.
1. import and export
2. using the following code, but it don't copy across any indexes etc.
select *
into database.owner.tablename
from table
MSDN : Select into[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
By performing the select, it does not copy over the indexes set up on the table, it just copies over the data.
All features/properties of the table needs to copied over?
|
|
|
|
|
|
Dear all,
am doing the follows:
Public Function totalsumbycontract(a As String, b As Integer) As Double
Set classdb1 = CurrentDb()
set classrecset1 = classdb1.OpenRecordset("select unit_price from contract_details where contract_number = ' " & a & " ' ")
end function
where a is the contract number, and b is contract id. the problem is when am runnig the query by using contract number, it returns 0 records, while by contract id , it works fine.
Contract number is defined in table desig as TEXT, and id is defined as number. also if i run the query on the table it self by contract_number , it works fine.
only when am using the query in this code , its not working.
Dunno wahts the mistake i did?? and have no clue ?? its just a simple line of code 
Can any body guide ??
0 will always beats the 1.
|
|
|
|
|
You wrote:
set classrecset1 = classdb1.OpenRecordset("select unit_price from contract_details where contract_number = ' " & a & " ' ")
Nothing to say without seeing your table.
Checkout "contract_number" is that string type or number. If Number type just remove the single quotes.
|
|
|
|
|
how do i get top 3 salaries among department, which is represented by employee with lowest 3 salaries (total 9 rows)
please reply.
|
|
|
|
|
Please rephrase your question or give some sample records as an example. It's hard to figure out what you really want.
|
|
|
|
|
Please explain ur question. Its not clear for me. Can u come up with any example?
abhi
|
|
|
|
|
It needs kind of presentation that what's your data and what you want as a final output.
Furthermore AFA I understood the problem I think you need to apply groupby in SQL and then after sort top 3 records among the retrieved result set.
further sample data can make me more clear regarding.
Regards,
Hiren.
be good(Help people in CP),do good(Vote if one finds helpful) all will happen good, In case happens bad(You are getting downvote for your best try to help OP) it will be good for later after(Countered with more points by humble member). - Gita sar in context of CP.
-So Guys don't care about downvote believe in you.
|
|
|
|
|
How to get data in financial year(april -march) in stored procedure in sql server 2005?I googled a lot.But didnt find suit answer.Hope you help. 
|
|
|
|
|
Something like this you mean?
Inputparameter is int @fiscalyear.
WHERE (YEAR(yourdatefield) >= @fiscalyear AND MONTH(yourdatefield) >= 4)
AND (YEAR(yourdatefield) <= (@fiscalyear + 1) AND MONTH(yourdatefield) <= 3)
Cheers
If you can read this, you don't have Papyrus installed
|
|
|
|
|
Because each company may have a different fiscal year, you need to actually have a strategy to deal with it in your data structure. there are a number of options!
Have a function that converts a date to finscalyear/period (2010/01 = April)
Actually store the correct fiscal values with the data.
Maintian a "Period" table with all the relevant dates and FK it to your data (we use this method)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
My company is currently running SQL Server 2005 and we have out main project database set up on it. Everyone in the company can connect to it through Access using a linked table. This was setup a little while back by another company. However my company has now asked me to create a small database on the server. So I logged into the server, created the database, added the needed columns and the data we have already accumulated that need to be in the database. However when I try to link it through Access it won't let me; the connection fails. I can't do it through the ODBC Data Source Administrator either. I did notice though that when I try and create a new data source through the ODBC Admin that regardless of whether I select SQL Server or SQL Server Native Client 10.0 our main project database comes up (it is set up though to use SQL Server not the Native Client), however the new database only comes up if I select SQL Server Native Client 10.0. I'm thinking, and hoping, this is why I can't connect to the database from my laptop but I'm not sure. Why does the new database only come up under the SQL Native, but the main project database comes up under both, and how can I make the new database come up under the SQL Server option? I've compared both database properties and everything seems to be exactly the same. I'm pretty new to SQL Server but the company has put this on me and given me a week to get it up. Any help would be greatly appreciated. Thanks.
Dominick
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
