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. [cc lang=”sql”] IF OBJECT_ID(N’tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [/cc] To replicate this, let’s run the following command in the same window multiple times: [cc lang=”sql”] — (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product [/cc] We get the error message: Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore. [cc lang=”sql”] 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 [/cc]
Continue reading ...
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: [cc lang=”sql”] — Copy all the records into a new table SELECT * INTO CustomerCopy FROM SalesLT.Customer — View the table SELECT * FROM CustomerCopy [/cc] 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: [cc lang=”sql”] 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] […]
Continue reading ...
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. [cc lang=”sql”] SELECT CASE WHEN ISNUMERIC(PostalCode) > 0 THEN CAST(PostalCode AS INT) ELSE 0 END FROM SalesLT.Address [/cc] 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. [cc lang=”sql”] — 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
Continue reading ...
To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view. [cc lang=”sql”] SELECT * FROM INFORMATION_SCHEMA.COLUMNS [/cc] 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. [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS’ [/cc]
Continue reading ...
To list all databases in a SQL Server instance, run the following: [cc lang=”sql”] SELECT * FROM sys.sysdatabases [/cc] This also outputs metadata about each database including createdate, compatibility level, and the MDF filename.
Continue reading ...
