How can I remove duplicate rows? What is the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows)? The rows of course will not be perfect duplicates because of the existence of the RowID identity field. MyTable ----------- RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null Answer: Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to

Inserting multiple rows in a single SQL query? I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Idand Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office"); Can I insert all 4 rows in a single SQL statement? Best Answer: In SQL Server 2008 you can insert multiple rows using a single SQL INSERT

How to return the date part only from a SQL Server datetime datatype SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 Answer: On SQL Server 2008 and higher, you should convert to date: SELECT CONVERT(date, getdate()) On older versions, you can do the following: SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date)) for example SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) gives me 2008-09-22 00:00:00.000 Pros: No varchar<->datetime conversions required No need to think about locale http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype

Concatenate many rows into a single text string? Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary? answer: I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily. If there is a table called STUDENTS SubjectID StudentName ---------- ------------- 1 Mary

Add a column with a default value to an existing table in SQL Server How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005? Solution: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [WITH VALUES]

Table Naming Dilemma: Singular vs. Plural Names Convention has it that table names should be the singular of the entity that they store attributes of. I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table to the singular, forever sentencing those using the table to sometimes have to use brackets. My gut feel is that it is more correct to stay with the singular, but my gut feel is also that brackets indicate undesirables like column names with

Delete or Drop all tables whose names begin with a certain string I would like a script to delet or drop all tables whose name begins with a given string. I'm sure this can be done with some dynamic sql and the INFORMATION_SCHEMA tables. If anyone has a script, or can tap one up quickly, please post it. If no-one posts an answer previous to I figure it out myself, I'll post my solution. Solution : You may require to modify the query to contain