|
You need to do it in steps:
Select * into #table from mytable where 1 = 2
insert into #table select distinct * from mytable
truncate table mytable
insert into mytable select * from #table
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Example Table :
Cl1 Cl2
ID SecndClmnNAme
Delete From Tab Where ID NOt In Select Distinct ID From Tab)
|
|
|
|
|
Are you sure this works? Surely, if there are duplicate rows there are duplicate ids?
I mean, if you have this
id name
1 bob
2 fred
1 bob
your query will not delete anything as all ids occur in the distinct.
Or have I missed something?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
delete from tablename where id not in (select distinct id from tablename )
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Are you sure this works? Surely, if there are duplicate rows there are duplicate ids?
I mean, if you have this
id name
1 bob
2 fred
1 bob
your query will not delete anything as all ids occur in the distinct.
Or have I missed something?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
you are not suppose to have that problem you know?!
my advice for you is to start all from the beginning!!!
Question yourself why you need that? can´t you achieve what you want to do without creating duplicate rows?
can you see that you are creating unnecessary procedures!
trying to help you there![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
how to restore database of sqlserver2005 into sqlserver2000
|
|
|
|
|
I am pretty sure you can't do this.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Cannot be done using restore. You can use SSIS, bcp or other tools to transfer data from 2005 to 2000.
The need to optimize rises from a bad design
|
|
|
|
|
hi all,
i am new to SQL Server
i want to Create backup of my database and restore it programmatically.
i search many example on Google and also on Code project(USING SMO object) but no link fulfill my requirement.Each example give error "Backup failed for Server 'MachineName\InstanceName'.
Then i use following stored procedure to create backup
Create PROCEDURE CreateBackupFile<br />
@BackUpPath varchar(200)<br />
AS<br />
BEGIN<br />
<br />
Backup Database Test to disk = @BackupPath <br />
END
above procedure works well when i run via VB.net
but now the problem with Restore.I use following statement
Create PROCEDURE RestoreBackupFile<br />
@RestoreFilePath varchar(200)<br />
AS<br />
BEGIN<br />
<br />
RESTORE DATABASE Vehicle<br />
FROM DISK = @RestoreFilePath<br />
END
but when i run this procedure then it give error "Database is use for this session. Use Master'
after that i modify above procedure as following
Create PROCEDURE RestoreBackupFile<br />
@RestoreFilePath varchar(200)<br />
AS<br />
BEGIN<br />
Use master<br />
Go<br />
RESTORE DATABASE Vehicle<br />
FROM DISK = @RestoreFilePath<br />
END
but now one more limitation appear that we can not use the 'Use database' statement within Stored procedure,function and trigger
Pleast suggest how can i resolve this problem.
|
|
|
|
|
RestoreBackupFile-procedure cannot be in the database you are restoring (restore command restores the database completely including stored procedures tables etc).
You must either create the procedure into another database or call the restore from the program.
The need to optimize rises from a bad design
|
|
|
|
|
i tried but now this give the error
"Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally."
i already close all connection to my database. Also i close my IDE.
Please suggest.
|
|
|
|
|
The reason for the error is that some connection is still using your database (you can use Management Studio to find out the connection).
If you want to remove the connections from your database, you can try adding
ALTER DATABASE Vehicle SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That closes all connections from the database and rolls back all active transactions.
The need to optimize rises from a bad design
|
|
|
|
|
Thanks mika for your quick response
i resolve the problem by using ClearAllPools procedure provided by .net.
one more thing ,Whether there are any other method for restore. Since this method require one more databse
Thanks a Lot
|
|
|
|
|
For actual restore there is no other method. You need two databases if you want to have a stored procedure which does the restore. If you place the restore command into an application, you won't need another database.
Depending what you want to achieve, you can search for alternatives for restoring a database, such as detach/attach database, loading data using SSIS or bcp etc.
The need to optimize rises from a bad design
|
|
|
|
|
Mika Wendelius wrote: If you place the restore command into an application, you won't need another database.
how can i achieve this thing ?I tried but 'Database is in use' error occured
|
|
|
|
|
Using .Net (SqlConnection and SqlCommand classes):
- connect to SQL Server
- use master database (or connect directly to it)
- optionally disconnect other users from your database if there are any using the command from my previous post
- execute the restore statement
- release the database to multi user state
So basically the logic is the same, but it isn't placed inside a stored proc but a calling aplication which doesn't connect to your database (Vehicle) but to master database.
The need to optimize rises from a bad design
|
|
|
|
|
hi Mika,
Thanks a lot.
i am stupid so i create temp database(and not use the master database).
Previously i use restore statement in code but with connection object i use the main database.
Now i use master database.
One more question what is the life time of this type of backup mean 1 day,2 day... (Please see my first post of this Thread for backup procedure)
once again
Thanks you very much
|
|
|
|
|
I don't quite understand what you mean with lifetime?
You can take a backup today and restore it back one year later if you want. However you will loose then all changes that have taken place within a year. So a backup file is valid as long as you keep it somewhere safe.
The need to optimize rises from a bad design
|
|
|
|
|
hai
I have the table mytable.
I want to indexing the mytable.
I want the queries for indexing,Searching the words in my table and Advanced Searching.
I have solution for webpages searching, words in files searching but
any where i dont find Database searching.
Thanks and Regards
--------------------
Rama koteswararao.y
|
|
|
|
|
There is no simple answer, what you are looking at is optimisation, and without knowing lots more information nobody can advise you. For example, how big is the table, what are the main queries going to be on?
Optimisation is a large subject with no cut and dried answers. If you are using sql server then look at the query plans, and sql server 2005 also has (under the Query menu) Analyze Query In database Engine Tuning Advisor which can give you some good guideline.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i cant find Tuning advisor give me correct path plz
|
|
|
|
|
Tuning advisor is under the Query Menu, but its not there if you nly have the express version.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Guys.I have a table in database Access with a Font(example:VN-Times).I want to bring all struct and data from Access to Sql with Font default in SQL(Unicode).Please help me how to do 
if you cound not try then
msgbox("you will lose")
else
msgbox("you can change yourself")
end if
|
|
|
|
|
Databases don't store fonts. You either have char/varchar (standard ascii characters) or nchar/nvarchar which is unicode.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
