I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it? Here are two possible ways of doing it. … Read more
What is the best way to remove duplicate rows from a fairly large SQL Server 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 … Read more
I need to update this table in SQL Server with data from its ‘parent’ table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.assid. What query will do this? I’m thinking of a join but I’m not sure if it’s … Read more
Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. without enough detail may be edited or deleted. Is it possible to query for table names which contain columns being LIKE ‘%myName%’ ? 3 33
I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains. Is there an easy way to determine how much space on disk each table is consuming? 2 26 SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows, SUM(a.total_pages) * 8 AS … Read more
How do I perform an IF…THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete=”N” OR InStock = ‘Y’ ? 1 : 0) AS Saleable, * FROM Product 3 32
How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but using a stored procedure that returns data, is not quite the same. select … Read more
This question already has answers here: Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement? (16 answers) Closed 8 years ago. I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES (“John”, 123, “Lloyds Office”); … Read more
What is the difference between LEFT JOIN and LEFT OUTER JOIN? 12 As per the documentation: FROM (Transact-SQL): <join_type> ::= [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ] JOIN The keyword OUTER is marked as optional (enclosed in square brackets). In this … Read more
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 How can I get that? 4 44
