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 coming from. There are actually two different ways to specify source data. One way is using a SELECT statement, and the other way using the VALUES statement. Inserting using the VALUES Statement The VALUES statement is typically used when either hard coding values to be inserted or when specifying variables to be inserted. The SELECT statement is typically used when querying data from an existing table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES(‘Bill’, ‘Jones’) [/cc] We see here that the VALUES statement takes the actual column values to be inserted into the Customer table. Using this method, you can only insert one row at a time. With the advent of SQL Server 2008, the VALUES statement can actually insert multiple rows at one time by delimiting with a comma. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) VALUES (‘Bill’, ‘Jones’) ,(‘John’, ‘Smith’) [/cc] INSERT INTO Using the SELECT Statement The select statement is generally used when selecting from the table. [cc lang=”sql”] INSERT INTO Customer ( FirstName ,LastName ) SELECT FirstName = t.First_Name ,LastName = t.Last_Name FROM Customers_Import t [/cc] This statement will actually insert all the customers from the Customers_Import […]
Continue reading ...
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 temporary table, then loops through rebuilding everything. [cc lang=”sql”] USE AdventureWorksLT2008 GO SELECT RowNum = ROW_NUMBER() OVER(ORDER BY t.TABLE_NAME) ,TableName = t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME ,AlterMe = ‘ALTER INDEX ALL ON [‘ + t.TABLE_SCHEMA + ‘].[‘ + t.TABLE_NAME + ‘] REBUILD;’ INTO #Reindex_Tables FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_TYPE = ‘BASE TABLE’ DECLARE @Iter INT DECLARE @MaxIndex INT DECLARE @ExecMe VARCHAR(MAX) SET @Iter = 1 SET @MaxIndex = ( SELECT COUNT(1) FROM #Reindex_Tables ) WHILE @Iter < @MaxIndex BEGIN SET @ExecMe = ( SELECT AlterMe FROM #Reindex_Tables WHERE RowNum = @Iter ) EXEC (@ExecMe) PRINT @ExecMe + ' Executed' SET @Iter = @Iter + 1 END [/cc]
Continue reading ...
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 year. A couple incidences similar to this has scared me enough to be very careful in my SQL Date comparisons. Most of the time when hard coding a date I define it out the way SQL Server defines it (at least according to SQL Profiler). Like this: [cc lang=”sql”] DECLARE @MyDate datetime SET @MyDate = ‘2000-01-04T00:00:00.000’ SELECT * FROM Customers WHERE ModifiedDate = @MyDate [/cc] When defined this way, SQL will have no option to interpret in any other way that what is presented. The downside is, yes, it’s long. However when hard coding dates, I’d personally rather be safe that sorry. I haven’t run into it, nor have I tested it, but I’d hate to have all my dates coded in a system changed just because the next version of SQL or any other system decided to interpret things differently. The other safe way to compare dates is to use SQL Server’s datepart function. Utilizing this method, you can never go wrong either. [cc lang=”sql”] SELECT * FROM Customers WHERE — Compare year DATEPART(yyyy, ModifiedDate) = 2000 — compare month AND DATEPART(mm, ModifiedDate) = 08 — compare day AND DATEPART(dd, ModifiedDate) = 01 [/cc] […]
Continue reading ...
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 [cc lang=”sql”] — SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.CustomerID ASC) ,c.* FROM SalesLT.Customer c [/cc] Method 2 There are two ways to do this in SQL 2000. The easiest way is to join the table on itself and count the number of rows that precede the primary key. [cc lang=”sql”] — SQL 2000+ SELECT Rownum = ( SELECT COUNT(1) FROM SalesLT.Customer WHERE CustomerID
Continue reading ...
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 back-end or maintenance related. Ok, now that I got that off my chest, here you go. In order to perform an execution which iterates performing a “for each row”, this is the easiest way for me: [cc lang=”sql”] SELECT RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID) ,* INTO #Customers FROM SalesLT.Customer DECLARE @MaxRownum int SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers) DECLARE @Iter int SET @Iter = (SELECT MIN(RowNum) FROM #Customers) WHILE @Iter
Continue reading ...
