Insert into Identity Column

January 22, 2010 melissasuciadi 1 comment

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. :D
SET IDENTITY_INSERT #Customer OFF
GO

-- Drop Customer table.
DROP TABLE #Customer
GO

Huraaah!!

How to convert time format to minutes/duration

January 22, 2010 melissasuciadi Leave a comment

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.

January 15, 2010 melissasuciadi Leave a comment

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 :D )

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.. :D

Select case when @i >8  OR @i <@x
 then convert(varchar(10),NULL)
 END AS 'Derived Column'
Categories: Uncategorized Tags: ,

DateTime to Varchar to Varbinary to Varchar to DateTime

January 14, 2010 melissasuciadi Leave a comment

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 .. :D

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

November 26, 2009 melissasuciadi Leave a comment

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

November 10, 2009 melissasuciadi 2 comments

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

October 9, 2009 melissasuciadi 3 comments

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
Categories: Transact SQL

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

October 7, 2009 melissasuciadi 5 comments

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