Delete Duplicate Rows
There are some cases when you need to do cleanse on your duplicate data.
Below I provide some way to clean your data.
here’s the duplicate data
--Customer's Telephone declare @t table (ID int identity (1,1), FName varchar(20), LName Varchar(20), Phone varchar(10), CreatedAt smallDatetime) insert into @t (FName, LName, Phone, CreatedAt) select 'Mary', 'Jane', '09876543' , dateadd( day, -33, GETDATE()) UNION ALL select 'Nan', 'Nes', '123456' , dateadd( day, -30, GETDATE()) UNION ALL select 'Jazz', 'Soy', '5748759' , dateadd( day, -29, GETDATE()) UNION ALL select 'Rocky', 'Sion', '875643' , dateadd( day, -27, GETDATE())UNION ALL select 'Mary', 'Jane', '1234567' , dateadd( day, -21, GETDATE()) UNION ALL select 'Rocky', 'Sion', '785465' , dateadd( day, -14, GETDATE()) UNION ALL select 'Jazz', 'Soy', '4536382' , dateadd( day, -8, GETDATE())UNION ALL select 'Mary', 'Jane', '6598690' , GETDATE() SELECT * FROM @t
here’s the data
ID FName LName Phone CreatedAt 1 Mary Jane 09876543 2009-10-24 14:24:00 2 Nan Nes 123456 2009-10-27 14:24:00 3 Jazz Soy 5748759 2009-10-28 14:24:00 4 Rocky Sion 875643 2009-10-30 14:24:00 5 Mary Jane 1234567 2009-11-05 14:24:00 6 Rocky Sion 785465 2009-11-12 14:24:00 7 Jazz Soy 4536382 2009-11-18 14:24:00 8 Mary Jane 6598690 2009-11-26 14:24:00
in data above we would like to take the latest phone number from the same Customer
expected result
ID FName LName Phone CreatedAt 2 Nan Nes 123456 2009-10-27 14:24:00 6 Rocky Sion 785465 2009-11-12 14:24:00 7 Jazz Soy 4536382 2009-11-18 14:24:00 8 Mary Jane 6598690 2009-11-26 14:24:00
first method : this method works for SQL 2005 and UP
--find the latest by id.. ;WITH DeleteDuplicate AS ( SELECT Fname, LName, Phone, ROW_NUMBER() OVER (PARTITION BY Fname, LName ORDER BY ID DESC) AS RowNumber FROM @t ) DELETE DeleteDuplicate WHERE RowNumber > 1; --find the latest by createdAt ;WITH DeleteDuplicate AS ( SELECT Fname, LName, Phone, ROW_NUMBER() OVER (PARTITION BY Fname, LName ORDER BY CreatedAt DESC) AS RowNumber FROM @t ) DELETE DeleteDuplicate WHERE RowNumber > 1;
2nd way : applicable in sql 2000
-- using max, aggregate function
delete @t
from @t A
where id not in (select MAX(id) as ID
from @t
group by FName, lname)
Create trigger to find out user updating table
Do you wish to know who do change to your table? Who is updating or do anything to your table? ya you can keep log from your app but how about if you wish to done anything on your db site? Then I suggest you to use trigger like below
--Create Trigger to insert lastmodifiedtime and lastmodifiedBy acc to SQL User CREATE TRIGGER LastModif ON TableName AFTER Updated AS Update A Set LastModifiedBy = suser_sname(), LastModifiedTime = getdate() FROM TableName A JOIN Inserted as U ON A.ID = U.ID
In code above you’ll see suser_sname. Suser_sname returns the login name associated with a security identification number (SID). So If you try
select suser_sname
It’ll displaey you the current account / domain you use.
more about suser_sname http://msdn.microsoft.com/en-us/library/ms174427.aspx
Create temporary table in dynamic query
there are cases when you are forced to create temporary table in dynamic query.
below is sample of creating temporary then select it in dynamic query.
declare @tableName varchar(30)
set @tableName = 'TableB'
declare @query nvarchar(4000)
set @query = 'CREATE TABLE #'+@tableName+'
( id int,
data varchar(30)
)
insert into #'+@tableName+' values(1, ''Me'')
SELECT * from #'+@tableName
exec sp_executesql @query
Find Character Occurence in String
I made this query to answer a challenge given from a programming forum. After the query is done I realized that I’ve made a mistake. And it was quite a mistake. The challenge ask you to list all the character that occur in sequence. What I made is a query to list the most sequential occurence character.. hahaha
I know this query is far from perfect. But just let me share it with you.
this is the data source
Data ------------------------------------ 9992EDC6-D117-4DEE-B410-4E5FAE46AE97 0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1 4A73E7EB-7777-4A04-9258-F1E75097977C 5AAF477C-274D-400D-9067-035968F33B19 725DA718-30D0-44A9-B36A-89F27CDFEEDE 8083ED5A-D3B9-4694-BB04-F0B09C588888 22244444-43B9-4694-BB04-F0B098888888
expected result :
Data pattern len pattern2 len2 ---------------------------------------------------------------------------- 9992EDC6-D117-4DEE-B410-4E5FAE46AE97 999 3 NULL NULL 0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1 NULL NULL NULL NULL 4A73E7EB-7777-4A04-9258-F1E75097977C 7777 4 NULL NULL 5AAF477C-274D-400D-9067-035968F33B19 AA 2 NULL NULL 725DA718-30D0-44A9-B36A-89F27CDFEEDE 44 2 NULL NULL 8083ED5A-D3B9-4694-BB04-F0B09C588888 88888 5 NULL NULL 22244444-43B9-4694-BB04-F0B098888888 8888888 7 NULL NULL DATA Char Pos Len ------------------------------------------------------------ 9992EDC6-D117-4DEE-B410-4E5FAE46AE97 9 1 3 0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1 NULL NULL NULL 4A73E7EB-7777-4A04-9258-F1E75097977C 7 10 4 5AAF477C-274D-400D-9067-035968F33B19 A 2 2 725DA718-30D0-44A9-B36A-89F27CDFEEDE 4 15 2 8083ED5A-D3B9-4694-BB04-F0B09C588888 8 32 5 22244444-43B9-4694-BB04-F0B098888888 8 30 7
My query
Create table #t (Data VARCHAR(40), pattern varchar(50), [len] int,
pattern2 varchar(50), len2 int )
INSERT #t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT #t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT #t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT #t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT #t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT #t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
INSERT #t (Data) SELECT '22244444-43B9-4694-BB04-F0B098888888'
select * from #t
declare @pattern varchar(20)
set @pattern = null
declare @maxlen int
declare @e int
declare @i varchar(2)
declare @j varchar(2)
set @maxlen = (select MAX(len(data)) from #t)
print @maxlen
declare @q nvarchar(4000)
set @i = 1
set @j = 2
while (@i < 36)
begin
print @i
set @q = '
update #t
set pattern = right(isnull(pattern,''''),isnull(len(pattern),1)-1) +
SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
and RIGHT(ISNULL(pattern, SUBSTRING(Data,'+@i+',1)),1) = SUBSTRING(Data,'+@i+',1)
and isnull(substring(data, '+@i+'-[len]+1, 1),SUBSTRING(data, '+@i+', 1))
= SUBSTRING(data, '+@i+', 1)
update #t
set pattern2 = right(isnull(pattern2,''''),isnull(len(pattern2),1)-1) +
SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
and RIGHT(ISNULL(pattern2, SUBSTRING(Data,'+@i+',1)),1)
= SUBSTRING(Data,'+@i+',1)
and isnull(substring(data, '+@j+'-[len2]+1, 1),
SUBSTRING(data, '+@i+', 1)) = SUBSTRING(data, '+@i+', 1)
and [len] != 0
update #t
set [len] = len(pattern),
len2 = len(pattern2)
update #t
set [pattern] = [pattern2],
[len]= len2,
pattern2 = NULL
where len2 > [len]
and SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)
update #t
set [pattern2] = NULL,
[len2]= NULL
where SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)
'
print @q
exec SP_executesql @q
set @i +=1
set @j +=1
end
select * from #t
select DATA, LEFT(pattern,1) AS 'Char', PATINDEX('%'+pattern+'%', DATA) AS Pos, [Len]
from #t
Basically what I am trying to do is compare the char with the following char. And Keep the pattern and if I found another sequential occurrence the most frequent will be recorded.
More about insert statement
In my previous post about inset I’ve given you some variation that can be done to insert data.
Now that I’ve wandered in some forums I’ve found another way to insert multiple data.
before
INSERT INTO TableName (Col1, Col2) VALUES (1, 'John'); INSERT INTO TableName (Col1, Col2) VALUES (2, 'Mike'); INSERT INTO TableName (Col1, Col2) VALUES (3, 'Jane');
another way I found
INSERT INTO TableName (Col1, Col2) SELECT 1 , 'John' UNION ALL SELECT 2 , 'Mike' UNION ALL SELECT 3 , 'Jane' --only in SQL Server 2008 INSERT INTO TableName (Col1, Col2) VALUES (1, 'John'), (2, 'Mike'), (3, 'Jane')
Problem in assigning aliases from variable
this is a case i found in a forum and I find it quite fascinating, so the case is you would like to assign alias to your column name based on value of other table. This person has a table for mapping between the column name and its alias.
Check the code below for my solution
CREATE TABLE [dbo].[#items]( [PK] [bigint] IDENTITY(1,1) NOT NULL, [User_Sequence] [int] NOT NULL DEFAULT ((1)), [User1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [User2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ) insert into #items(User_Sequence,User1,User2) select 1,'Apple','Green Apple' union all select 2,'Orange','Citrus' CREATE TABLE [dbo].[#fields]( [FLD$TABLE_NAME] [varchar](100) NOT NULL, [FLD$FIELD_NAME] [varchar](50) NOT NULL, [FLD$CAPTION] [varchar](50) NOT NULL, ) insert into #fields(FLD$TABLE_NAME,FLD$FIELD_NAME,FLD$CAPTION) select '#items','User_Sequence','Seq' union all select '#items','User1','Type' union all select '#items','User2','Description' declare @tableName nvarchar(20) set @tableName = '#items' declare @oldname varchar(20), @newname varchar(20) declare @qEX nvarchar(4000) set @qEX ='SELECT PK ' Declare curcolumn Cursor FOR select [FLD$FIELD_NAME], [FLD$CAPTION] from #fields where [FLD$TABLE_NAME] = @tableName OPEN curcolumn FETCH NEXT FROM curcolumn INTO @oldName, @NewName WHILE @@FETCH_STATUS = 0 BEGIN SET @qEX += ', ['+@OLDNAME+'] AS ['+@NewName+'] ' FETCH NEXT FROM curcolumn INTO @oldName, @NewName END CLOSE curcolumn DEALLOCATE curcolumn SET @qEX += ' from '+@tableName+' ' PRINT @qEX EXEC sp_EXECUTESQL @QEX
Replace Part of String (T-SQL)
Having trouble replacing part of expression in a field?
I had that problem once and along my journey to find the solution to my problem I found this.
REPLACE ( string_expression , string_pattern , string_replacement )
example :
SELECT REPLACE('abcdefghicde','cde','xxx');
result :
------------
abxxxfghixxx
if the pattern you’re lookin for not there then
SELECT REPLACE('abcdefghicde','XYZ','xxx');
--it'll replace nothing
result :
------------
abcdefghicde
source : msdn
SQL return *
What happen if you try to convert an int with the length of character 5 to varchar (3)? I think it’ll cut the int to 3 char length or it’ll come out as an error something like invalid length of character.
my assumption : 1. 10000 > 100 2. error message : invalid length...
And my assumption is wrong.. 
here’s what really happen
SELECT CONVERT(int, 1) AS Integer
UNION ALL
SELECT 10000
SELECT CONVERT(varchar(3), Integer) AS String
FROM (SELECT CONVERT(int, 1) AS Integer
UNION ALL
SELECT 10000
) AS S
integer
-------
1
10000
result
string
-------
1
*
the conclusion is int is one of datatype that can convert to varchar but since the length of varchar assigned is shorter than the input SQL cannot convert it without making missing in information.
So if you found case like this don’t panic. Just add more to your varchar length.
another experiment I tried. I tried to convert back the query above to int
SELECT CONVERT(int, A.String) FROM
(SELECT CONVERT(varchar(3), Integer) AS String
FROM (SELECT CONVERT(int, 1) AS Integer
UNION ALL
SELECT 10000
) AS S) A
it give me error :
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '*' to data type int.
Coalesce another IsNull method
COALESCE ( expression [ ,...n ] )
Coalesce is Equivalent with
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ... ELSE expressionN END
My little experiment with coalesce
DECLARE @i VARCHAR(20) DECLARE @exp2 SET @exp2 = NULL SET @i = null SELECT COALESCE (@i + ' more;', 'It''s Null') --result [It's Null] SELECT COALESCE (@i , ' more;', 'Stranger') --result [ more;] SELECT COALESCE (@i , @exp2, 'Stranger') --result [Stranger] SET @i = 'Something' SELECT COALESCE (@i + ' more;', 'It''s Null') --result [Something more;] SELECT COALESCE (@i , ' more;', 'Stranger') --result [Something] SELECT COALESCE (@i , @exp2 , 'Stranger') --result [Something]
As you see if I use case and combine it with null function it’ll need more than one line. Coalesce is very useful and helpful in above case.
For More Reading Read : http://msdn.microsoft.com/en-us/library/ms190349.aspx — Coalesce (Transact-SQL)
Insert Statement
This is a very basic SQL query but often asked.
How to Insert from other table that already exists?
The basic Insert statement is.
CREATE TABLE Customer (
ID int identity(1,1),
CustomerName varchar(30),
Address varchar(100),
Phone varchar (100)
)
INSERT INTO Customer (CustomerName, Address, Phone)
values ('Jane', 'anywhere street', '9097655')
How about you want to put or just copy some of the data from other table to this Customer Table?
INSERT INTO Customer (CustomerName, Address, Phone) SELECT CustomerName, Address, Phone From OldCustomer Where ID > 50
In above query you’ll insert your customer table with data from OldCustomer table with ID less than 50 (0-49).
How about if you just want to create a replication of a table with data type?
On
SELECT * INTO newTable FROM OldTable
You don’t need to create the table first. Cause on select into statement the create table is already done then the insertion.
SELECT * INTO Customer FROM OldCustomer
How about if you just want to create a replication of some column in a table and columns’ data type?
SELECT CustomerName, Phone INTO Customer FROM OldCustomer
And if there are needs to use join or where clause just use it as you need it. 
example :
SELECT A.CustomerName, A.Phone, B.City INTO Customer FROM OldCustomer A JOIN City B On A.CityID= B.CityID WHERE City LIKE ':%' INSERT INTO Customer (CustomerName, Address, Phone) SELECT CustomerName, Address, Phone From OldCustomer A JOIN City B On A.CityID= B.CityID Where A.ID > 50 AND City LIKE 'L%'
for more about this topic see http://msdn.microsoft.com/en-us/library/aa933206%28SQL.80%29.aspx
