Merge, Using, When, Output – sql server

;MERGE RUO_ITEM AS rsRUO

USING @ruoItems AS updatedRUO

ON (rsRUO .FieldId = updatedRUO.FieldId )

 

WHEN MATCHED THEN

UPDATE

SET

      rsRUO .EntityType = updatedRUO.EntityType ,

      rsRUO .MasterEntityTypeId = updatedRUO.MasterEntityTypeId ,

      rsRUO .FieldValue = updatedRUO.FieldValue ,

      rsRUO .RetailElementId = updatedRUO.RetailElementId ,

      rsRUO .FieldName = updatedRUO.FieldName ,

      rsRUO .ValueSeq = updatedRUO.ValueSeq ,

      rsRUO .CreateDatetime = updatedRUO.CreateDatetime ,

      rsRUO .ChangedDatetime = updatedRUO.ChangedDatetime ,

      rsRUO .RowStatus = updatedRUO.RowStatus ,

      rsRUO .ChangedById = updatedRUO.ChangedById

 

WHEN NOT MATCHED THEN

INSERT (EntityType ,EntityTypeId, MasterEntityTypeId,RowSeq , RetailElementId,FieldValue, FieldName,ValueSeq , CreateDatetime,ChangedDatetime, RowStatus,ChangedById )

VALUES(updatedRUO .EntityType, updatedRUO.EntityTypeId ,updatedRUO. MasterEntityTypeId,updatedRUO .RowSeq, updatedRUO.RetailElementId, updatedRUO.FieldValue ,updatedRUO. FieldName,updatedRUO .ValueSeq, updatedRUO.CreateDatetime, updatedRUO.ChangedDatetime ,updatedRUO. RowStatus,updatedRUO .ChangedById)

output

            inserted .EntityType,     

            inserted .EntityTypeId,     

            inserted .RowSeq,    

            inserted .RetailElementId ,    

            inserted .FieldValue ,     

            inserted .FieldName ,     

            inserted .ValueSeq ,     

            inserted .CreateDatetime ,     

            inserted .ChangedDatetime ,    

            inserted .RowStatus,

            inserted .ChangedById

 into @tmpOutputTable ( 

                        EntityType ,    

                        EntityTypeId ,    

                        RowSeq ,   

                        RetailElementId ,

                        FieldValue ,    

                        FieldName ,    

                        ValueSeq ,    

                        CreateDatetime , 

                        ChangedDatetime ,

                        RowStatus, changedbyid)

Dynamic Query

declare @tablename nvarchar (100)

set @tablename = ‘party’

 

declare @query nvarchar (1000)

set @query= ‘select top 10 * from ‘ + @tablename

exec (@query )

— Get the dynamic query value assigned to a outer variable

DECLARE @RsDataSQL nvarchar(2000)=‘select top 1 @Rstable2 = ”RS_” + rc.TablePrefix + ”_” + rf.TablePrefix + ”_EventType”’

       +‘ from RControl rc 

      inner join Form rf  on rf.FormId=rc.FormId

      where rf.partyid = 49310′            

 

declare @lrstable nvarchar (500) = ”

exec sp_executesql @RsDataSQL, N’@Rstable2 nvarchar(500) out’, @lrstable out

 

select @lrstable

— another example

declare @tablename nvarchar (100)

set @tablename = ‘party’

 

declare @query nvarchar (1000)

set @query= ‘select top 1 @partyid = partyid from ‘ + @tablename

 

declare @partyid1 int = ”

exec sp_executesql @query, N’@partyid int out’, @partyid1 out

 

select @partyid1

Get , sep value from table column

SELECT STUFF(

(SELECT ‘,’ + s.PerfIndDesc

FROM table s

ORDER BY s.id

FOR XML PATH( ”)),1 ,1, ”)

GO

Paged results – sql server

;with a(

rcontrolid,logoname

)as (

select top 20 RControlId,LogoName  from RControl),

b(

RowNumber,rcontrolid ,logoname

)AS ( SELECT ROW_NUMBER () OVER (ORDER BY RControlId ASC ) AS RowNumber,* FROM a)

select * from b where RowNumber between 2 and 6

Custom Table Type – sql server

IFNOTEXISTS(SELECT*FROM sys.types st JOIN sys.schemas ss ON st.schema_id= ss.schema_id WHERE st.name =N’IdVal’ AND ss.name =N’dbo’)

CREATE TYPE [dbo].[IdVal] AS TABLE (

      [Id] [int] NOT NULL,

      [Val] [nvarchar] (100) NOT NULL

)

GO

/*

declare @Table dbo.IdVal

insert into @Table

      select 1,’val1′

            union

      select 2,’val2′

select * from @Table

exec somesp @TableInput=@Table

CREATE PROCEDURE somesp   

@IdVal IdVal READONLY

AS

……

 

*/

SQL Search – Link

https://www.red-gate.com/products/sql-development/sql-search/

SQL Search From Red Gate To search across Sql Server using plain string

SQL Search – quickly find SQL in SSMS

•     Find fragments of SQL text within stored procedures, functions, views and more
•     Quickly navigate to objects wherever they happen to be on a server
•     Find all references to an objec
• SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases.

http://www.red-gate.com/products/sql-development/sql-search/

Find a Table/Table Column usage across DB – SQL Server

— Following query is helpful to find all db objects that used to set value for the given columnname of a table …

select distinct name,o.type
from syscomments c
join sysobjects o on c.id = o.id
where TEXT like ‘%TableName%’ and TEXT like ‘%Set%ColumnName%’
order by name

— Finding table dependencies using SP

sp_depends ‘<Table Name>’

Set/Reset SQL Server DB To Single User Mode – CMD

1) ALTER DATABASE DBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

2) exec sp_dboption ‘DBNAME’, ‘single user’, ‘FALSE’

SQL SERVER – PIVOT and UNPIVOT Table Examples – Link

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/