SQL Server Insert and Delete

1. how to insert five rows in a table using single query(in ms-sql or oracle)
2.how to delete duplicate values in a table.

for example,
the table containing five duplicate values(like name rani).
i would like to delete all four rows in a table. but not the one row.

srnsundari
Profile Answers by srnsundari Questions by srnsundari
Dec 22nd, 2008
4
2344

Questions by srnsundari

SQL Server

Answer

Showing Answers 1 - 4 of 4 Answers

cybersavvy
Profile Answers by cybersavvy

Feb 19th, 2009

To delete the duplicate rows in the table.

delete from table_name1 where rowid<(select max(rowid) from table_ name2 where table_name1.columnname = table_name2.columnname)

krishna8081
Profile Answers by krishna8081

Feb 24th, 2009

How to insert data in multiple tables at same time

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

How to delete duplicate values in a table
Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)

clickfreehit
Profile Answers by clickfreehit

Apr 11th, 2009

Inset data:

insert into targettable
select top 5 * from sourcetable

delete data:
1. select distinct * into targettable from sourcetable

2. delete from sourcetable

3. insert into sourcetable
select * from targettable

psingla
Profile Answers by psingla Questions by psingla

Mar 19th, 2010

As suggested by krishna
create table test_a
(a int
)
insert into test_a values(1)
insert into test_a values(1)
insert into test_a values(3)
insert into test_a values(4)
insert into test_a values(5)
insert into test_a values(1)
insert into test_a values(2)
insert into test_a values(3)
insert into test_a values(4)
insert into test_a values(5)

WITH [test_a rowid]
     AS (SELECT a.a,
                Row_number()
                  OVER(ORDER BY a.a) num
         FROM   test_a a)
DELETE FROM [test_a rowid]
WHERE       num NOT IN (SELECT Rank()
                                 OVER(ORDER BY b.a)
                        FROM   test_a b)