|
Do you want to compare the contents of the tables. If that's the case I think the quickest way could be to use EXCEPT[^].
If the table structure is the same, you could do comparison like:
SELECT * FROM FirstTable
EXCEPT
SELECT * FROM SecondTable
and then other way round:
SELECT * FROM SecondTable
EXCEPT
SELECT * FROM FirstTable
If there are structural differences, specify the columns you want to compare.
|
|
|
|
|
Are you talking about comparing the structure or the data? They require 2 different strategies.
Do you want to do this for 1 pair of table or are you looking to compare an entire database? They require 2 different strategies.
If you need to compare databases then look at Red-Gate sql and data compare tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be more clear, I would like compare the data with in two tables of same design. Would like to report all rows that are different.
|
|
|
|
|
Thanks so much, EXCEPT really works, and it is really fast as well.
|
|
|
|
|
|
|
|
folder where files arrive in every two hrs.
Sp i created read files in every 2 hrs from folder and moves files in separate folder.
When reading files it inserts approx 11k - 12k lines in table.
Now this has to be run everyday at every two hour.
So, What measure should i take from slowing down queries result as days, months, year passes by?
One i have in mind run a SP at every 10-15 day interval which will copy this table data into another table and removes rows?
Your ideas ?
|
|
|
|
|
If I understood correctly you're concerned about the performance when the amounts of data grow. Based on the rough information you provided, few opinions:
- correct indexing strategy must be implemented (depending on the needs)
- I wouldn't use separate tables since it would affect program logic
- think carefully about the table structure, optimize it for typical use-cases
- consider using partitioning.
|
|
|
|
|
You got it right.
I think about moving data to separate table as query which has to run on table, will need only information of 1 previous day , today, 1 after day on any given day.
Like if run my queries today i.e 09 Mar 11 17:04 PM
then query will look for data of 8, 9, 10 march
that's why i think about moving data after 10 days to separate table.
Mika Wendelius wrote: - correct indexing strategy must be implemented (depending on the needs)
- I wouldn't use separate tables since it would affect program logic
- think carefully about the table structure, optimize it for typical use-cases
If apply all these will it still work after 5 year (assuming 12000 * 12 * 365 * 5 rows) ?
Mika Wendelius wrote: consider using partitioning.
I don't have any clue about this?
Will be helpful if you point to an article (targeting less then have DBA exp)
|
|
|
|
|
Hum Dum wrote: I think about moving data to separate table as query which has to run on table,
will need only information of 1 previous day , today, 1 after day on any given
day.
This makes things even easier and goes back to good indexing. So if the new rows are always added to the end in your timeline, you could consider using clustered index. This would make queries selecting the near past very efficient, see: http://msdn.microsoft.com/en-us/library/ms190639.aspx[^]
Hum Dum wrote: If apply all these will it still work after 5 year (assuming 12000 * 12 * 365 *
5 rows) ?
Roughly 25 million rows, as long as the structure and indexing are well done you should be safe, of course depending on the requirements 
Basically the earlier rows are kinda a dead mass if you don't query them as long as you have a good access path to the few rows you need
Hum Dum wrote: don't have any clue about this?
Will be helpful if you point to an article
(targeting less then have DBA exp)
Here's one starting point: http://msdn.microsoft.com/en-us/library/ms178148.aspx[^]
Hopefully these help,
mika
|
|
|
|
|
What I have done in the past is to periodically delete rows older than some threshold. If you like, you can use a trigger to also write them to some archive table.
|
|
|
|
|
Hello Everybody,
I have generate a problem While Aliasing Query.
SQL Command is : Select S_no as S. No. From Emp;
If you can think then I Can.
|
|
|
|
|
the alias name should be a valid identifier, no punctuation, no spaces,...
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Select S_no as [S. No.] From Emp;
would work but as Luc said that its not best practice.
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.
|
|
|
|
|
It's Not working..............
If you can think then I Can.
|
|
|
|
|
what sort of error message did you get?
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.
|
|
|
|
|
Error is :
Invalid bracketing on name 'S. No.'.
If you can think then I Can.
|
|
|
|
|
try
select columnName as S_NO from tablename
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.
|
|
|
|
|
You're real problem is that you are putting UI information into the query, this type of formatting belongs in your user interface no the database
Oh and this does work in sql server 2005/08
SELECT ship [S. No.] FROM Ship AS S
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding quotation marks around the alias:
Select S_no AS "S. No." From Emp;
|
|
|
|
|
In SQL query '.' can be used to get a column from a table...
For eg : "Select Employee.EmpId from Employee"
So Sql always expects a column name after a '.' , use some other characters as alias names.
This would solve your problem
|
|
|
|
|
Use a valid identifier. Dot is not a valid identifier.
|
|
|
|
|
read complete summery of this question. Do't waste your time in Closed Questions.
If you can think then I Can.
|
|
|
|
|
Let's say I have a publisher and subscriber database set up. If I want to say, add a column to a table in publisher, will the change automatically be replicated to the subscriber database? Or is that only for data?
"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
