Insert into Identity Column
You create a table with identity column, You accidentally delete one of its row instead update it.. If you insert new column it’ll only increment to the last id inserted+1. You need it to be a specific id. So you try to insert it with the id specified but you cannot do that you get error.. So how to insert column to identity column?
CREATE TABLE #Customer(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into #Customer table.
INSERT INTO #Customer(Name) VALUES ('Jake')
INSERT INTO #Customer(Name) VALUES ('Suzan')
INSERT INTO #Customer(Name) VALUES ('Marry')
INSERT INTO #Customer(Name) VALUES ('Sally')
GO
-- Accidentaly delete Marry (oh nooo I just want to add Marry's Maiden name!?!?!)
DELETE #Customer
WHERE Name = 'Marry'
GO
SELECT *
FROM #Customer
GO
-- Try to insert Marry again to ID 3;
-- got an error.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT #Customer ON
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO #Customer (ID, Name) VALUES (3, 'Marry Marc')
GO
SELECT *
FROM #Customer
GO
--Don't forget to set the identity insert back off. 
SET IDENTITY_INSERT #Customer OFF
GO
-- Drop Customer table.
DROP TABLE #Customer
GO
Huraaah!!
How to convert time format to minutes/duration
Have you ever face trouble when you need to convert values like ‘00:45:00.00′ to 45 minutes or ‘01:23:00.000′ to 83 minutes?
How do you do it?
here’s the input :
declare @time datetime set @time = '01:45:00.000'
This is the first method, the idea is using datepart function to get the hour part and minute part of the data and multiply the hour by 60 then add it with the minute part.
select datepart(hour,@time) AS hour, datepart(MINUTE,@time) AS min, datepart(hour,@time)*60 +datepart(MINUTE,@time) As Duration
the 2nd method, I like this one better cause it’s simpler. The idea is looking the difference in minutes from 00:00:00.000 represented by 0 to the data..
Select DATEDIFF(minute, 0, @time) As Duration
error :Msg 8133, Level 16, State 1, Line 7 ; None of the result expressions in a CASE specification can be NULL.
above error is happen because the SQl is confuse what is the datatype for the result expression in the case statement..
here’s simple script to produce the same error and the solutiooooon (at least it worked for me )
declare @i int set @i = 3 declare @x int set @x = 6 Select case when @i >8 OR @i <@x then NULL END AS 'Derived Column' ----error :Msg 8133, Level 16, State 1, Line 7 ----None of the result expressions in a CASE specification can be NULL.
ALTERNATIVE :
Select case when @i >8 OR @i <@x then 'Something' --different value END AS 'Derived Column'
Why It Worked : because you assign one of the value to varchar so sql thought ok the null is varchar, follow the other value)
second alternative :
Select case when @i >8 OR @i <@x then NULL Else 'X' -- put dummy thingy END AS 'Derived Column'
Why it worked : It Worked because you don’t even try to assign it to NULL value
But but I need to assign it to NULL and no other value and I don’t want and else..
AHAAA…
Select case when @i >8 OR @i <@x then cast(NULL as int) END AS 'Derived Column'
why why ? here you cast the result to a datatype (here i cast it to int, sql is no longer confuse)..
No nooo.. I want assign it to a varchar then so be it..
Select case when @i >8 OR @i <@x then convert(varchar(10),NULL) END AS 'Derived Column'
DateTime to Varchar to Varbinary to Varchar to DateTime
My first post on 2010.. Yay!! The First is convert between datatypes. The Idea is convert from datetime to varchar to binary to varchar again to datetime ..
DECLARE @DateWithDateTime DATETIME SET @DateWithDateTime = '2010-09-08 00:00:00:00' DECLARE @Date varchar(200) SET @Date = (SELECT CONVERT(VARCHAR(20), @DateWithDateTime, 120)) SELECT CONVERT(VARBINARY(100), @Date)AS theVarbinary --theVarbinary --0x323031302D30392D30382030303A30303A30303A3030 SELECT convert(VARCHAR(200),CONVERT(VARBINARY(100), @Date)) AS BackTodate --BackTodate --2010-09-08 00:00:00:00
hey where is back to datetime.. hahaha it’s just to complete the cycle(feels good)..
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
