Insert Results of Query Into Table

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. 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 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: SELECT * INTO dbo.EventLogCopy FROM dbo.EventLog

Continue reading ...

Concatenate Rows

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. SELECT     ColumnList = SUBSTRING(                 (SELECT                     ‘,’ + ISNULL(President,”)                 FROM dbo.Presidents                 FOR XML PATH(”))             ,2,8000) 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. ;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 The next two examples require a variable, and when using a variable you […]

Continue reading ...

SQL Cast Date

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: — remove the time SELECT CAST(GETDATE() AS DATE) — 2009-07-12 — remove the date SELECT CAST(GETDATE() AS TIME) — 08:46:25.8130000 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. — 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) Cast Date with No Time Using Convert Using convert you can remove the time for display purposes, usually for reporting or the front end. — 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 Roll Your Own Date To roll your own date format, use the datename function. This is also the only way to get the full month name. […]

Continue reading ...

Insert Carriage Return Line Feed to String

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. 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 Output: [code] Line One. Line Two. Line Three. [/code]

Continue reading ...

SQL Server Pagination using CTE and RowNumber

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. 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)

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!