SQL Server Planet

Select Distinct a way to tell SQL Server to not return duplicate rows. Adding the distinct keyword makes it an expensive query because it essentially performs a group by for all of the returned columns. That being the case, it should only optimally be used on queries that return a small number of rows, or small number of columns.

When using Select Distinct, you should keep in mind that this is generally a workaround to either improper joining of tables, or an improper where clause that is not limiting the result set to only returning one instance of a record.

In order to utilize distinct, all you need to do, is place it before the select list.

SELECT DISTINCT *
FROM yourtable

This will surely only return a unique instance of every record in the result.

If you’re here then you’ve probably run into the situation where you’ve automatically created a temp table in your script, and every time you execute the script you have to drop the temp table manually. Yes, this can be a pain. I’ve done this myself many times.

So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
	DROP TABLE #Temp
END

To replicate this, let’s run the following command in the same window multiple times:

-- (Replace the product table below with your table)
SELECT *
INTO #Temp
FROM SalesLT.Product

We get the error message:

There is already an object named '#Temp' in the database.

Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
	DROP TABLE #Temp
END

-- (Replace the product table below with your table)
SELECT *
INTO #Temp
FROM SalesLT.Product

The method of inserting records from one table to another differs depending on whether or not the target table already exists. Neither method is difficult, however one method requires more coding.

Insert where target table does not exist

If it does not, then you are in luck, there is a method that could not be simpler using SELECT..INTO. Select into automatically creates a table based on the result of a select statement. This select statement can be as simple or complex as you like. The only think you need to do, is add the line: INTO TABLENAME right above the FROM statement. Here goes:

-- Copy all the records into a new table
SELECT *
INTO CustomerCopy
FROM SalesLT.Customer

-- View the table
SELECT *
FROM CustomerCopy

It should be noted that this method does not create any keys that may be existing from the old table to the new table.

Insert where target table does exist

It’s not that this method is more difficult, it just requires more code. To do this, you need to use the INSERT INTO statement. When using this method, you need to specify the column names of the target table you want to insert, and the column names of the source table you are inserting from. Here is an example:

INSERT INTO [AdventureWorksLT2008].[SalesLT].[Customer]
(
	[NameStyle]
	,[Title]
	,[FirstName]
	,[MiddleName]
	,[LastName]
	,[Suffix]
	,[CompanyName]
	,[SalesPerson]
	,[EmailAddress]
	,[Phone]
	,[PasswordHash]
	,[PasswordSalt]
	,[ModifiedDate]
)

SELECT
	[NameStyle]
	,[Title]
	,[FirstName]
	,[MiddleName]
	,[LastName]
	,[Suffix]
	,[CompanyName]
	,[SalesPerson]
	,[EmailAddress]
	,[Phone]
	,[PasswordHash]
	,[PasswordSalt]
	,[ModifiedDate]
  FROM [AdventureWorksLT2008].[SalesLT].[Customer2]

The brackets surrounding the column names above is optional. If you are using SQL Server Management Studio (ssms), the easiest way to do this is to right click on the table name, choose ‘Script Table As’ -> ‘Create To’ -> ‘New Query Window’. This will automatically generate the insert code for all the column names to insert into. The only thing you have to do is fill in your select statement.

Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer.

SELECT
	CASE WHEN
		ISNUMERIC(PostalCode) > 0
	THEN CAST(PostalCode AS INT)
	ELSE 0 END
FROM SalesLT.Address

Now, what if the column values contain text characters and you want to strip those characters out and then convert the value to an integers. Well, luckily, there is a way to do this. Most of the examples to do this will loop the values using a while loop, however this example uses a dynamic numbers table in conjunction with a trick shown to me by Simon Sabin from his blog. This method will replace all the non numeric characters and convert the values to an integer.

-- define max number of character values
-- in the string being evaluated
DECLARE @MaxNumber INT = 5000

;WITH Numbers AS
(
    SELECT 1 AS Num
    UNION ALL
    SELECT Num+1
    FROM Numbers
    WHERE Num <= @MaxNumber
)

SELECT
	CAST
	(
		(
			SELECT CASE
				WHEN SUBSTRING(PostalCode,Num,1) LIKE '[0-9]'
				THEN SUBSTRING(PostalCode,Num,1)
				ELSE '' END
			FROM Numbers
			WHERE Num <= LEN(PostalCode)
			FOR XML PATH('')
		) AS int
	 )
FROM SalesLT.Address

OPTION(MAXRECURSION 32767)

To make this work with your example, simply replace the value [PostalCode] with your field and the FROM Clause should be your table you are querying from.

To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

In order to get all the columns in all the databases however, you need to loop through the databases. To do this, you can use the undocumented sp_MSForEachDB procedure that Microsoft ships.

sp_MSForEachDB @command1='USE ?;

SELECT
	Table_Catalog
	,Table_Schema
	,Table_Name
	,Column_Name
	,Data_Type
	,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS'