Using the Merge Statement’s OUTPUT Clause to Match Inserted IDENTITY Fields with Source Table Fields

With this project I needed to insert data into table1 from table2 while simultaneously retrieving the identity field of table1, then insert that identity into table3 which mapped to table2 on a key that table1 did not have. The OUTPUT clause when used with the input statment would not facilitate this because it could not return the identity and the source key in the same row. The result was to use the SQL Server 2008 Merge statement with the output clause. Traditionally I would have looped the insert one record at a time and returned back both the values into variables, then inserted the identity into table3 by joining the keys on table2 and table 3. [cc lang=”sql”] CREATE TABLE #MatchIDs ( Table_1_EmployeeID int, Table_2_EmployeeID int ) INSERT INTO #MatchIDs ( Table_1_EmployeeID, Table_2_EmployeeID ) SELECT mrg.Table_1_EmployeeID ,mrg.Table_2_EmployeeID FROM( MERGE dbo.Table_1 t1 USING dbo.Table_2 t2 ON 1 = 0 WHEN NOT MATCHED THEN INSERT ( FirstName ) VALUES ( t2.FirstName ) OUTPUT $action ,t2.EmployeeID ,t1.EmployeeID ) AS mrg ( output_action ,Table_1_EmployeeID ,Table_2_EmployeeID ) WHERE mrg.output_action = ‘INSERT’ [/cc] The result is a statement that actually fools the MERGE by intentionally not matching any rows and just inserting all the records into the table. 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: [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 […]

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

Find Resource Currently Involved in Blocking

[cc lang=”sql”] SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id [/cc]

Find Queries Using Most CPU

[cc lang=”sql”] SELECT TOP 5 object_name(objectID) ,[Avg CPU Time] = total_worker_time/execution_count ,execution_count ,Plan_handle ,query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC; [/cc] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!