Transfer table into different Schema :

Select table name with Schema
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'orders'
GO

Create new Schema with some name:
create schema new_schema
Now transfer table into new schema:
alter schema new_schema
transfer dbo.Orders
go
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM   sys.tables
WHERE  name = 'orders'
GO

Update Trigger:

----main table---
create table Trigger_update
(
Sl_no int unique identity(1,1),
Col1 varchar(30),
Col2 varchar(30),
Col3 varchar(30),
)
insert into Trigger_update values('Masum','Reza','Test1')
insert into Trigger_update values('Masum1','Reza2','Test2')
insert into Trigger_update values('Masum2','Reza3','Test3')
insert into Trigger_update values('Masum3','Reza4','Test4')

select * from Trigger_update
----Trigger table---
create table Trigger_update_trig
(
Sl_no int,
Col1 varchar(30),
Col2 varchar(30),
Col3 varchar(30),
Sys_User varchar(30),
Operation varchar(30)

)
select * from Trigger_update_trig
--Creating trigger for Update--
create trigger update_trig on Trigger_update
after update
as
declare
@sl_no int,
@col1 varchar(30),
@col2 varchar(30),
@Col3 varchar(30),
@user varchar(30),
@Operation varchar(30);

if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @Operation = 'UPDATE';
    SET @user = SYSTEM_USER;
    SELECT @sl_no = Sl_no from inserted i;
select @col1=Col1 from inserted
select @col2=Col2 from inserted
select @Col3=Col3 from inserted
    INSERT into Trigger_update_trig(Sl_no,Col1, Col2,Col3, Sys_User,Operation) 
values (@sl_no,@col1,@col2,@Col3,@user,@Operation);
end
Conclusion :
If Update operation will exist in main table then all records will insert into trigger table.

Check Duplicates URL present in a same row with the delimiter of "|" :

;with cte
as
(
SELECT ID,URL, SUBSTRING(URL, 1, CASE CHARINDEX('|', URL)
            WHEN 0
                THEN LEN(URL)
            ELSE CHARINDEX('|', URL) - 1
            END) AS FirstPart
    ,SUBSTRING(URl, CASE CHARINDEX('|', URL)
            WHEN 0
                THEN LEN(URL) + 1
            ELSE CHARINDEX('|', URl) + 1
            END,10000) AS LastPart
FROM #Temp
)
select * from cte where FirstPart=LastPart
Output:

String pattern between two columns and update third column with matched and unmatched values:

create table #temp_eg(Col1 varchar(50),Col2 varchar(50))

alter table #temp_eg add ID int unique identity(1,1)

insert into #temp_eg values('60t,50o,40c,','60t,50o,40c,76yu,gh89,')

insert into #temp_eg values('60t,50o,100c,','60t,50o,40c,100c,gh89,')

insert into #temp_eg values('100rs,200rs,300rs,','300rs,200rs,400rs,500rs,600rs,',null)

select * from #temp_eg

alter table #temp_eg add [Results] varchar(50)

Execute the below code and see the results.

declare @s1 varchar(50)

declare @s2 varchar(50)

declare @c int

set @c=1

declare @count int

select @count=count(Distinct ID) from #temp_eg

while(@c<=@count)

begin

select @s1=col1 from #temp_eg where ID=@c

select @s2=Col2 from #temp_eg where ID=@c

declare @t1 table (word varchar(50))

while len(@s1)>0

begin

    if (CHARINDEX(',', @s1)>0)

    begin     

 insert into @t1 values(ltrim(rtrim(LEFT(@s1, charindex(',', @s1)))))       

 set @s1 = LTRIM(rtrim(right(@s1, len(@s1)-charindex(',', @s1))))

    end

    else

    begin

        insert into @t1 values (@s1)

        set @s1=''    

    end    

end

declare @t2 table (word varchar(50))

while len(@s2)>0

begin

    if (CHARINDEX(',', @s2)>0)

    begin     

  insert into @t2 values(ltrim(rtrim(LEFT(@s2, charindex(',', @s2)))))       

  set @s2 = LTRIM(rtrim(right(@s2, len(@s2)-charindex(',', @s2))))

    end

    else

    begin

        insert into @t2 values (@s2)

        set @s2=''    

    end    

end

--SELECT * FROM @t1

--SELECT * FROM @t2

select word into #temp from @t2 where  word not in (SELECT word FROM @t1)

union all SELECT * FROM @t1

--select * from #temp

declare @output as varchar(50)

select @output=STUFF((SELECT ' ' + word

            FROM #temp

            FOR XML PATH('')) ,1,0,'')

   --select @output

update #temp_eg set [Results]=@output where ID=@c

set @c=@c+1

delete from @t1

delete from @t2

drop table #temp

end

Output:

or we can use this Function also:

https://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/

String pattern between two columns and update something if one string is present in another column on same row:

create table StringMatch(ID int unique identity(1,1),String1 varchar(30),String2 varchar(30))

select * from StringMatch 

insert into StringMatch values('my word is my power','my power is my word') 

insert into StringMatch values('the way of mine','the mine of way') 

insert into StringMatch values('they are playing','are them playing') 

insert into StringMatch values('how are you today','today you are how') 

insert into StringMatch values('stop thinking','do your thoughs') 

alter table StringMatch add [Status] varchar(10)

select * from StringMatch

Now i want to update status if String1 is matching with String2 column .
Check below pasted code :

declare @s1 varchar(50)

declare @s2 varchar(50)

declare @c int

set @c=1

declare @count int

select @count=count(Distinct ID) from StringMatch

while(@c<=@count)

begin

select @s1=String1 from StringMatch where ID=@c

select @s2=String2 from StringMatch where ID=@c

declare @t1 table (word varchar(50))

while len(@s1)>0

begin

    if (CHARINDEX(' ', @s1)>0)

    begin   

insert into @t1 values(ltrim(rtrim(LEFT(@s1, charindex(' ', @s1)))))  

set @s1 = LTRIM(rtrim(right(@s1, len(@s1)-charindex(' ', @s1))))

   end

    else

    begin

        insert into @t1 values (@s1)

        set @s1=''    

    end   

end

declare @t2 table (word varchar(50))

while len(@s2)>0

begin

    if (CHARINDEX(' ', @s2)>0)

    begin      

insert into @t2 values(ltrim(rtrim(LEFT(@s2, charindex(' ', @s2)))))       

set @s2 = LTRIM(rtrim(right(@s2, len(@s2)-charindex(' ', @s2))))

   end

    else

    begin

        insert into @t2 values (@s2)

        set @s2=''    

    end    

end

--SELECT * FROM @t1

--SELECT * FROM @t2

--select case when exists(SELECT * FROM @t1 EXCEPT SELECT * FROM @t2) then 'are not' else 'are equal' end

update StringMatch set [status]=

case when exists(SELECT * FROM @t1 EXCEPT SELECT * FROM @t2) then 'are not' else 'are equal' end

where ID=@c

set @c=@c+1

delete from @t1

delete from @t2

end

Output Table :

Transpose single column into a single row:

create table #temp(Word varchar(30))
insert into #temp values('word1')
insert into #temp values('word2')
insert into #temp values('word3')
insert into #temp values('word4')
insert into #temp values('word5')

SELECT STUFF((SELECT ' ' + word 

            FROM #temp

            FOR XML PATH('')) ,1,0,'') AS word

String matching between two strings:

The below code very useful for me when i was searching some requirement as per job.

Then i got this code from stack overflow. In this below scenario one string is matching with another if it is matched then return something else something.

declare @s1 varchar(50) = 'my word is my power'
declare @s2 varchar(50) = 'my power is my word'
declare @t1 table (word varchar(50))
while len(@s1)>0 
begin
    if (CHARINDEX(' ', @s1)>0)
    begin       
        insert into @t1 values(ltrim(rtrim(LEFT(@s1, charindex(' ', @s1)))))        
        set @s1 = LTRIM(rtrim(right(@s1, len(@s1)-charindex(' ', @s1))))
    end
    else
    begin
        insert into @t1 values (@s1)
        set @s1=''      
    end     
end
declare @t2 table (word varchar(50))
while len(@s2)>0 
begin
    if (CHARINDEX(' ', @s2)>0)
    begin       
        insert into @t2 values(ltrim(rtrim(LEFT(@s2, charindex(' ', @s2)))))        
        set @s2 = LTRIM(rtrim(right(@s2, len(@s2)-charindex(' ', @s2))))
    end
    else
    begin
        insert into @t2 values (@s2)
        set @s2=''      
    end     
end
select case when exists(SELECT * FROM @t1 EXCEPT SELECT * FROM @t2) then 'are not' else 'are equal' end

Simple Cursor to print Line by Line from a table:

I have create an employeeData table.

From this table i want to print Emp_Salray one by one by the use of cursor.

Declare @sal int

declare Cursor_Sal_Sum cursor read_only

for

select Emp_Salary from employeeData

open Cursor_Sal_Sum

fetch next from Cursor_Sal_Sum into @sal

while @@FETCH_STATUS=0

begin

print @sal

fetch next from Cursor_Sal_Sum into @sal

end

close Cursor_Sal_Sum

deallocate Cursor_Sal_Sum

Output :

23000

10000

Use of COLUMNS_UPDATED () in a Trigger :

--Creating Trigger--
Create trigger [dbo].[IF_Update_Trigger_Trig]
on [dbo].[IF_Update_Trigger]
after update as
if(COLUMNS_UPDATED() & 10)>0
begin
----inserting old values
insert into IF_Update_Trigger_Audi
(
ID,Changed_Status,Name,Emp_code,Salary,Sys_User,Modified_Date
)
select 
d.ID,
'OLD',
d.Name,
d.Emp_code,
d.salary,
SYSTEM_USER,
CURRENT_TIMESTAMP 
from deleted d;
--inserting updated values
insert into IF_Update_Trigger_Audi
(
ID,Changed_Status,Name,Emp_code,salary,Sys_User,Modified_Date
)
select 
i.ID,
'New',
i.Name,
i.Emp_code,
i.Salary,
SYSTEM_USER,
CURRENT_TIMESTAMP 
from inserted i;
end
--Main Table--
create table IF_Update_Trigger
(
ID int unique identity(1,1),
Name Varchar(30),
Emp_code int,
Salary int
)
---Inserting records into main table---
insert into IF_Update_Trigger values('Masum',123,5000)
insert into IF_Update_Trigger values('Reza',124,6000)

--Tracker Table--
create table IF_Update_Trigger_Audi
(
ID int,
Changed_Status varchar(10),
Name varchar(30),
Emp_code int,
Salary int,
Sys_User varchar(30),
Modified_Date date
)

--Updating main table

update IF_Update_Trigger set Salary=1000 where Salary=5000
update IF_Update_Trigger set Salary=2000 where Salary=6000

--Updates main table and all effected records are inserted into the tracker table.

select * from IF_Update_Trigger
select * from IF_Update_Trigger_Audi order by Modified_Date desc

Select rows with same id but different value in another column

create table #temp(ID int,Name varchar(10))

insert into #temp values(1,'Masum')

insert into #temp values(1,'Reza')

insert into #temp values(2,'Masum')

insert into #temp values(2,'Masum')

insert into #temp values(3,'Masum')

insert into #temp values(3,'Reza')

SELECT ID,Name FROM #temp WHERE ID IN

 (

SELECT a.ID FROM #temp a

JOIN #temp b on b.ID = a.ID AND b.Name <> a.Name

)order by ID

Now i want to select Distinct ID from the duplicates ID. In this case CTE is more usefull to fetch the records.

with cte

as

(

SELECT ID,Name FROM #temp WHERE ID IN (

    SELECT a.ID FROM #temp a

    JOIN #temp b on b.ID = a.ID AND b.Name <> a.Name

)

)

select distinct ID from cte

What is View and how it is working:

View is similar like a mirror of an object. View is a virtual table where the physical data can be reflect from the real table. Most of the people are talking about Update , Insert and Delete operation in Views. Can we perform Update or Insert or Delete operation in views?

As per my knowledge we can't perform on it. How and why please check the below scenario. Views it does mean we are selecting data from a table into a virtual table. So we can't perform Update,Insert or Delete operation by the time of creation.

create view Select_exmaple

as

select FirstName,LastName from Employee

The above snippets showing FirstName, LastName from  Employee.

Now i am trying to create a new View with Update statement.

create view Update_T

as

update Employee set LastName='BBB' where LastName='CCC'

Following error will raise :

Msg 156, Level 15, State 1, Procedure Update_T, Line 35

Incorrect syntax near the keyword 'update'.

It meaning we can not perform Update statement by the time of creating Views. Similarly if try to insert some values into views we will get same error.

Once you create the View with select statement then you can use Update,Insert and Delete Query for that particular View.

update Select_exmaple set LastName='CCC' where LastName='BBB'

The above statement will effect to the physical table  as per updated in views.

Cursor to select last performed row values:

DECLARE @MYVAR NVARCHAR(100)
Declare @date datetime
Declare @Host varchar(100)
DECLARE MYTESTCURSOR CURSOR
DYNAMIC 
FOR
SELECT Your_Col,HOST_NAME() FROM Your_tab
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @MYVAR,@Host
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @MYVAR,@Host