|
This works fine.
But that was just an example. In real application I have many columns, so I tried with #table, which works fine, but unable to update primey key column. Please check previous reply.
Kind regards
Agha
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
unable to update primey key column
modified on Saturday, October 4, 2008 11:28 AM
|
|
|
|
|
Yes, since the primary key is autogenerated, you cannot insert into it or update it. Can't you list all of the columns instead of * and leave the primary key out? Like:
INSERT INTO Test (NotKeyColumn1, NotKeyColumn2, ...)
SELECT NotKeyColumn1, NotKeyColumn2, ... FROM #table
An alternative could be that you drop the key column from temp table before insert. Something like:
...
UPDATE #table SET ourData1 = ourData1 + 1
ALTER TABLE #table DROP COLUMN KeyColumn
INSERT INTO test SELECT NULL, * FROM #table
However, I'm not sure if it works. I added NULL to the beginning, because if you do not list the target columns in insert, you must supply a value for all columns (in the order they appear in the target table).
The need to optimize rises from a bad design
|
|
|
|
|
Thank you.
There is no need for adding null,
but it works very well.
Thank you again.
Kind regards
Agha
|
|
|
|
|
Thank you.
It works.
Best regards
Agha
|
|
|
|
|
You're welcome.
The need to optimize rises from a bad design
|
|
|
|
|
Can any one tell the query to delete only duplicate rows from any table ?
|
|
|
|
|
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
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
|
|
|
|