delete from table1 where EmpID in(select cId from table1 group by cid having count(*) >1)
Asp.net Solution | sql tips
Here Reader, We are provide all question yhose asked in interview. you are read this blog and prepare for asp.net interview.
get only unique row and delete all duplicate row
delete duplicate value in sql server
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY column1,
column
)
Rename database in sql 2008
Rename Database in sql server 2008
1. How to create database in sql server 2008
USE Master;
Go
CREATE DATABASE
SALARYMANAGEM;
Go
2. How
to rename database
ALTER DATABSE
SALARYMANAGEM
MODIFY NAME=SALAYMANAGEMANT;
MODIFY NAME=SALAYMANAGEMANT;
Go
3. How
to drop database
Drop Database SALAYMANAGEMANT;
Go
Deepak Goyal
Cursor in SQL
We
can say that cursor is row pointer in a set of rows (result set).Which points a
single row at time in result set. But can move to other rows of the (result
set) when required.
To use cursor in SQL Store procedures. You
need to follow the given steps.
- Define a cursor
- Open the cursor to establish the result set
- Fetch the data into local parameter as needed from the cursor.(one row at a time)
- Close the cursor when done.
Remove All User Defined Store procedures, Views and Triggers
interesting
things in SQL if you have a list of
Store Procedures ,Views and triggers in
your database and reason being you want
to delete all Store procedures means you want to delete all the thing from data
base. In that condition what you will do? It quiet simple you will delete one
by one all the things. But it is so boring and time wasting. Let see how to
write a simple Script to delete all Store procedures, views and triggers from
database.
Remove
All User Defined Store procedures.
Create PROCEDURE [dbo].[usp_RemoveAllStoreProceduers]
AS
BEGIN
declare @spName varchar(150);
--Step 1 : Define Cursor (Create a result set of all
procedures in database).
declare cur cursor for select [name] from sys.objects where type='p'
--Step 2 : Open Cursor
open cur
--Step 3 : Fatch data into local parameter
fetch next from cur into @spName
while @@fetch_Status
=0
begin
-- Drop current store procedure in Cursor
exec ('drop procedure '+
@spName)
fetch next from cur into @spName
end
close cur
--Close Cursor After done
deallocate cur
--Deallocate Cursor After done
END
Remove
All User Defined View.
Create PROCEDURE [dbo].[usp_RemoveAllUserDefinedViews]
AS
BEGIN
declare @viewName varchar(150);
--Step 1 : Define Cursor (Create a result set of all views in database).
declare cur cursor for select [name] from sys.objects where type='v'
--Step 2 : Open Cursor
open cur
--Step 3 : Fatch data into local parameter
fetch next from cur into @viewName
while @@fetch_Status
=0
begin
-- Drop current view in Cursor
exec ('drop view '+ @viewName)
fetch next from cur into @ viewName
end
close cur
--Close Cursor After done
deallocate cur
--Deallocate Cursor After done
E.net training in jaipur
Subscribe to:
Posts (Atom)