The “insert into” statement is used in order to insert data into an existing table. The syntax for this is fairly simple. In the first section of the statement, you specify the table name and column names in which you are inserting data into. The second part is where the source of the data is [...]
Archive of entries posted on June 2010
Alter Index All Tables
This script comes in handy when needing to alter all indexes in a database and rebuild them. This will only work on SQL Server 2005+. It utilizes the ALL keyword in the Alter index statement to rebuild all the indexes for a particular table. This script retrieves all base tables and stores them into a [...]
SQL Date Comparison
When comparing the datetime datatype in SQL Server, it is important to maintain consistency in order to gaurd against SQL interpreting a date differently than you intend. In at least one occasion I have seen someone specify a short format for a date, like (1/4/08) only to find that SQL interpreted the month as the [...]
Rownum in SQL Server
Prior to SQL Server 2005, there was no inherent function to generate a rownumber within a row. There is a workaround however for SQL 2000. If you are on SQL 2005+, then you will utilize the following function: Method 1 — SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.CustomerID ASC) ,c.* FROM SalesLT.Customer [...]
SQL Server For Each Row Next
It is difficult for me to write this particular article and I’ll tell you why. If you don’t care then just skip down to the example, but here goes anyway. It is very rare that you should have to perform looping in SQL. There are certain situations that do require it, and they are mostly [...]
The EXECUTE permission was denied on the object
This error is pretty self explanatory. If you are here, I’m sure you simply want to know how to correct it, so here goes. The user context in which you are executing does not have the rights to execute the procedure or function. So first, in order to find out which user you are, simply [...]
SQL Rank
The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions. RANK DENSE_RANK ROW_NUMBER NTILE This list may seem overwhelming, however most of the ranking functions [...]
SQL Server SELECT INTO
The most efficient method of copying a result set into a new table is to use the SELECT INTO method. This method also follows a very simple syntax. SELECT * INTO dbo.NewTableName FROM dbo.ExistingTable Once the query above is executed, all the columns and data in the table ExistingTable (along with their datatypes) will be [...]
SQL Server Begin Try
The try catch methodology of programming is a great innovation for SQL 2005+. The first question you should ask yourself before using Try/Catch should be “why?”. Why am I going to use Try/Catch? Personally, I have found a few uses, however I must say I do fall into the category of not being a developer [...]
Find Duplicate Fields in a Table
A common scenario when querying tables is the need to find duplicate fields within the same table. To do this is simple, it requires utilizing the GROUP BY clause and counting the number of recurrences. For example, lets take a customers table. Within the customers table, we want to find all the records where the [...]
