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.

Questions by srnsundari

Showing Answers 1 - 12 of 12 Answers

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)

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

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)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions