There are a few ways to insert query results into another table. The most common way is to use the standard insert statement. This would insert query results into a table that already contains data. [cc lang=”sql”] INSERT INTO dbo.Users ( Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName ,UpdatePassword ) SELECT Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName = DisplayName + ‘ SuperUser’ ,UpdatePassword FROM dbo.NewUsers nu WHERE nu.IsSuperUser = 1 [/cc] This way is most efficient if you are inserting records into a new table. It will automatically create the table for you and copy the datatypes from the existing table and all the rows: [cc lang=”sql”] SELECT * INTO dbo.EventLogCopy FROM dbo.EventLog [/cc]
Continue reading ...
This solution does not require a variable and it also allows you to do joins against other tables within one statement. The substring function removes the leading comma. Download the presidents table here. [cc lang=”sql”] SELECT ColumnList = SUBSTRING( (SELECT ‘,’ + ISNULL(President,”) FROM dbo.Presidents FOR XML PATH(”)) ,2,8000) [/cc] Using a common table expression (CTE) you can also recursively union the CTE with itself in order to generate a comma delimited list. This is a useful function for a CTE. The CTE itself does concatenate more than what is needed however it’s still relatively clean. Download the presidents table here. [cc lang=”sql”] ;WITH PresidentList (PresidentNumber, President) AS ( SELECT 1 , CAST(” AS VARCHAR(max)) UNION ALL SELECT cte.PresidentNumber + 1 ,cte.President + pl.President + ‘, ‘ FROM ( SELECT RowNum = Row_Number() OVER (ORDER BY PresidentNumber) ,President FROM dbo.Presidents ) pl JOIN PresidentList cte ON pl.RowNum = cte.PresidentNumber ) SELECT TOP 1 President FROM PresidentList ORDER BY PresidentNumber DESC [/cc] The next two examples require a variable, and when using a variable you cannot display any other columns from the table in the result set. There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE. [cc lang=”sql”] DECLARE @ColumnList varchar(8000) SET @ColumnList = ” — Set this to empty […]
Continue reading ...
Using SQL Server 2008 This first option of removing the date uses the SQL Server 2008 method. This will return only the date or only the time: [cc lang=”sql”] — remove the time SELECT CAST(GETDATE() AS date) — 2009-07-12 — remove the date SELECT CAST(GETDATE() AS time) — 08:46:25.8130000 [/cc] If you’re not working with SQL Server 2008, you have to improvise. Cast Date With No Time Using Floor This example removes the time from the date time by setting it to the beginning of the day. [cc lang=”sql”] — Get the current day with no time — 2009-06-29 00:00:00.000 SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) — Get the next day — 2009-06-30 00:00:00.000 SELECT CAST(CEILING (CAST(GETDATE() AS float)) AS datetime) [/cc] Cast Date with No Time Using Convert Using convert you can remove the time for display purposes, usually for reporting or the front end. [cc lang=”sql”] — Month first SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009 SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009 SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009 SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009 — Year first SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29 SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29 SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629 — Day first SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009 SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009 SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009 SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009 — Time only SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16 SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203 [/cc] Roll Your Own Date To roll your own date format, use the datename function. This is also […]
Continue reading ...
Here is a clean and efficient way to embed carriage returns into a string. I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. This replace function also comes in handy in most instances where you need to append strings. [cc lang=”sql”] declare @Note varchar (200) SET @Note = ‘Line One.[crlf];Line Two[crlf]Line Three.’ SET @Note = REPLACE(@Note,'[crlf]’,CHAR(13)+CHAR(10)) PRINT @Note [/cc] Output: [code] Line One. Line Two. Line Three. [/code]
Continue reading ...
This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) select * FROM SQLPaging where ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc]
Continue reading ...
