SQL Server - SQL Server Insert and Delete

GeekInterview.com > Tech FAQs > SQL Server

Go To First | Previous Question | Next Question

SQL Server | Question 62 of 68

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.

Total Answers and Comments: 3

Last Update: April 14, 2009 Asked by: srnsundari

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

February 19, 2009 15:15:41

cybersavvy

Member Since: February 2009 Contribution: 16

RE: SQL Server Insert and Delete

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)

Is this answer useful? Yes | No

February 24, 2009 13:18:45

krishna8081

Member Since: February 2009 Contribution: 2

RE: SQL Server Insert and Delete

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)

Is this answer useful? Yes | No

April 11, 2009 14:38:58

clickfreehit

Member Since: April 2009 Contribution: 2

RE: SQL Server Insert and Delete

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank