Mass Transfer Data

Here my tips for newbie in sql server to transfer massive data from table A to table B.

example using adventuresworks from sql server 2005 database sample.

select * into table1
from HumanResources.Employee
where employeeid between 1 and 20

i create table1 with the same field as HumanResources.Employee. Above query just an example.

--- use it if you need to update some field of records in table1 that may need to be updated if
-- there's exists on destination table.
update HumanResources.Employee set vacationhours = 24
from HumanResources.Employee a
inner join tabel1 b on a.employeeid=b.employeeid

-- here the main massive insert. the order of the field may the same as the destination table.
-- in case there's different, but some of the them have the same field than you need to add
-- (nmfield1, nmfield2 , .. before select statement ) before the select statement.
-- Its better if you defined the field that you want to insert

insert into tabel1
select a.*
from HumanResources.Employee a
left join tabel1 b on a.employeeid=b.employeeid
where b.employeeid is null

Have a try and good luck .

Happy New Years 2009

Using Default Parameter in Store Procedure

Sometime you need to create parameter with default value in your store procedure. Here is a simple example of how to make it:

create proc dbo.sp_getemployee
@employeeid char(10)= ''
as
select * from master_employee
where employeeid = case when @employeeid = '' then employeeid else @employeeid end

In here, i make a default value which is an empty string. I'm using it to manipulate the data that if the value is an empty string than return all employee data from employee table.

Now i can use it as below :

exec sp_getemployee

which is return all the record from master_employee

or

exec sp_getemployee @employeeid = '000000001'

which is return record with employeeid = '000000001'

How owsome isn't it ? but be carefule, it doesn't work if you create a report using Crystal report. Because, the crystal report will still ask to fill the value into the paramater.

Ok, now you know how to make default value into parameter in store procedure. Try it ok. Have a nice day

Store Procedure ( The Beginning )

Stored Procedure ( next i call it SP ) is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Syntax

CREATE PROC [ EDURE ] procedure _name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
(source SQL Help documentation)

That's a brief description and syntax about Stored Procedure.

Simple sample code :

/*
created : 10-12-2008 by Wihendro
Note : here you explain about the procedure function and purpose
*/
Create Proc dbo.spr_Test
as
set nocount on

select * from orders

That's all the brief SP best on my experience.
The /*...*/ ( a comment )filled in SP use to help us to monitor the changes made by the programmer. It may help us to track back the purpose of the SP. In application development using sql server as database, it may contain a lot of SP. After a decade of the development, if you need to change your code, you may encounter some difficulty about the purpose of the SP. So, in my opinion, you should use a comment above your SP.

"set nocount on" syntax use to set the message from sql server to off. every single statement either success or failed to execute, the sql server may send the message as part of the result execution. I used set nocount on to set it off. despite of receiving it, you may hide it. because you don't need it from your SP.

Ok, that's all for this time. For whom that doesn't know the SP , at least now know how to create it. and for others, may use my tips to add the comment and "set nocount on" on your SP. Wait for my next post more about Stored Procedure.

Have a Nice Day

Check .Net Framework 3.5 Compatibility Platform

To install .net framework 3.5 at server you have to check your platform compatibility. Yesterday, i tried to install .Net framework 3.5 to my server. What i was not realize is that the server platform is Windows 2000 Server, unlucky me. Check this link to check the platform compatibility : http://msdn.microsoft.com/en-us/library/bb882520.aspx

Experience is a good teacher, that's right. So, before you choose what programming language and IDE to develop a Website project, do the following steps:
1. Choose programming language and IDE. in my case, i use VS 2008 for Asp.net with .net Framework 3.5
2. Check your server compatibility needed to deploy your Website.

Have a nice day.

Kamen Rider's Series

it's along time ago, i falling love to Kamen rider series. Due to luck of information i just known some of them. Now, i took some time to search and listing it to have it :D.
This is a short list that i had know :
1. Kamen Rider Black
2. Kamen Rider Black Rx
3. Kamen Rider 555
4. Kamen Rider Kuuga
5. Kamen Rider Kabuto
6. Kamen Rider Hibiki
7. Kamen Rider Kiva
8. Kamen Rider Den-O
9. Kamen Rider Blade

I'm still searching the oldest one.
New update

Shōwa era

• 1971: Go Go Kamen Rider
• 1972: Kamen Rider Vs. Shocker
• 1972: Kamen Rider Vs. Hell Ambassador
• 1973: Kamen Rider V3
• 1973: Kamen Rider V3 vs. the Destron Monsters
• 1974: Kamen Rider X
• 1974: Kamen Rider X: The Five Riders Vs. King Dark
• 1975: Kamen Rider Amazon
• 1975: Kamen Rider Stronger
• 1980: Kamen Rider: Eight Riders vs. Galaxy King
• 1981: Kamen Rider Super-1
• 1988: Kamen Rider BLACK: Hurry to Evil Island
• 1988: Kamen Rider BLACK: Fear! Evil Monster Mansion
• 1989: Kamen Rider: Stay in the World - 3-D theme park special

Heisei era

• 1993: Kamen Rider ZO (1)
• 1994: Kamen Rider J (1)
• 1994: Kamen Rider World - 3-D theme park special
• 2001: Kamen Rider Agito: Project G4
• 2002: Kamen Rider Ryuki: Episode Final
• 2003: Kamen Rider 555: Paradise Lost
• 2004: Kamen Rider Blade: Missing Ace
• 2005: Kamen Rider Hibiki & The Seven Fighting Demons
• 2005: Kamen Rider The First
• 2006: Kamen Rider Kabuto: God Speed Love
• 2007: Kamen Rider Den-O: I'm Born!
• 2007: Kamen Rider The Next
• 2008: Kamen Rider Den-O & Kiva: Climax Deka
• 2008: Kamen Rider Kiva: King of the Castle in the Demon World
• 2008: Farewell, Kamen Rider Den-O: Final Countdow

Source (http://en.wikipedia.org/wiki/Kamen_Rider_Series#TV_series)

Return XML in Sql Server 2005

one of the feature in sql server 2005 is returning the query in XML format.

Syntax :
[ FOR { BROWSE | } ]
::=
XML
{ {
RAW [ ( 'ElementName' ) ] | AUTO }
[
[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] }
] [ , ELEMENTS [ XSINIL | ABSENT ] ]
| EXPLICIT
[ [ , XMLDATA ]
]
| PATH
[ ( 'ElementName' ) ]
[

[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
Source: SQL Help Documentation

simple example :

select top 100 contactid, firstname, middlename, lastname
from Person.Contact
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL

At .net application we only have to consume the xml result. How to do it, i will post it later because i still exploring it :D

Reset Visual SourceSafe 6.0 Admin

Forgot your visual sourcesafe 6.0 admin ? i got the way after along time googling, now i will share it to you in case some day i forgot it again :D.

It's simple, just create a new sourcesafe database but don't forget the admin password. Than go to Data Folder ( your new database sourcesafe folder ) and find Um.dat. Copy the file to Data Folder ( the database sourcesafe folder which you want to open). Don't forget to backup the original first. After that try to log on using the new one.

Have a nice day.

Taxed Income Function: Case Indonesia

Base on UU PPh pasal 21 in Indonesia, every working citizen have to pay income tax with progressive calculation tariff. The detail of this UU PPh will not discuss in here. What i will try to discuss is how to implemented it into sql server function dan store the tariff into tabel.

Even the progressive calculation tariff may stay for along time, it may possible that the rate may change or the placement. Real case is, the change of PPh 21 before 2008 change from 5 placement into 4 placement on 2009. You can imagine how you have to change all your calculation in your salary system if you use "fix code" method in your calculation system.

This is my idea to make the function and store the tariff into table. I Hope it will help you to efficient your job.

Here is some step you need to consider:
1. The function is for progressive tariff policy
2. There is a table to store the rate. it will be explain later.
3. The calculation is for Taxed Income, i will used PKP as the variable.

Firstly, we create the table:

CREATE TABLE [dbo].[Master_Tarif_Pajak](
[Tahun] [char](4) NOT NULL,
[Tingkat] [int] NOT NULL,
[Lower] [decimal](18, 0) NULL,
[Upper] [decimal](18, 0) NULL,
[Pajak] [decimal](5, 2) NULL,
CONSTRAINT [PK_Master_Tarif_Pajak] PRIMARY KEY CLUSTERED
(
[Tahun] ASC,
[Tingkat] ASC
)ON [PRIMARY]
) ON [PRIMARY]

The Requirement is :
Entry the record from the first placement to the fifth and the last one the upper we leave it Zero
Your data my looks like this :

Tahun Tingkat Lower Upper Pajak
2008 1 0 25000000 0.05
2008 2 25000000 50000000 0.10
2008 3 50000000 100000000 0.15
2008 4 100000000 200000000 0.25
2008 5 200000000 0 0.35
2009 1 0 50000000 0.05
2009 2 50000000 250000000 0.15
2009 3 250000000 500000000 0.25
2009 4 500000000 0 0.30

(9 row(s) affected)

Next step is the function :

/*
created : 29-08-2008 by wihendro

*/
create function dbo.UDF_Calculate_Tax
(
@pkp money , @tahun char(4)
)
returns money
as
begin
declare @tingkat int , @pajak money, @lower money, @upper money, @pajak1thn money
, @terhitung money, @denda money

set @tingkat = 1
set @pajak1thn = 0
set @terhitung = 0

while exists( select tingkat from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat
)
begin
select @lower = [lower], @upper = [upper] , @pajak = pajak
from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat


if @pkp > @upper
begin
if @upper = 0
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung)*@pajak)
break
end
else if @lower is not null
begin
if (@terhitung + ( @upper - @terhitung )) <= @pkp begin set @pajak1thn = @pajak1thn + ((@upper - @terhitung)*@pajak) set @terhitung = @terhitung + (@upper - @terhitung ) end else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
end
else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung) * @pajak )
break
end
set @tingkat = @tingkat + 1
set @lower = null
set @upper = null
end

return @pajak1thn
end

Example:

select Pajak = dbo.UDF_Calculate_Tax(200000000,'2008' )

Result :
Pajak
---------------------
36250000.00

(1 row(s) affected)

So, if next year, the role change into 4 placement ( see the sample data that i have created for you ), you only have to fill the tariff to the table and in your only have to send the value 2008 as the year. More effectively if you make an entry modul for the tariff table. So prepare your income tax calculation program to overcome the new UU Pph next year.

Note: You need to entry the new tariff every new begging year. Just don't forget to maintain the table ok.

Go to try it.
Good Luck.

User Defined Function

In every sql statement you made, sometime there's sql statement that you use over and over. In a big project scale, there's a good idea to put it into Store Procedure or Function, depends on your needs. This time, i will discuss about Function,use to be konwn as a User-Defined Functions ( UDFs ).

User-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. UDFs divide into 3 types, which are Scalar Functions, Inline table-valued and multiline table-valued

1. Scalar Functions
Syntax:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
(Source : SQL Help Documentation)

sample :

create function dbo.udf_hitung_score
(
@nilai numeric
)returns char(11)
as
begin
declare @grade char(1)

set @grade = case when @nilai between 85 and 100 then 'A'
when @nilai between 70 and 84 then 'B'
when @nilai between 60 and 69 then 'C'
when @nilai between 50 and 59 then 'D'
else 'F' end
return(@grade)
end

Example :

select dbo.udf_hitung_score(82)

You can use it as follow :
example, you have Table A with attribut Mark contain Student Mark with Numeric data type, than your query may become like this :

select nama_mahasiswa, nama_matakuliah, kelas , grade = dbo.udf_hitung_score(nilai)
from A
order by kelas

2. Inline Table-Valued Functions
syntax:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_Master_customers
(
@customerid nchar(10)
) RETURNS TABLE
as
RETURN (select * from customers
where customerid = @customerid )

example :

select * from dbo.udf_Master_customers('Alfki')

you can join it to with others table.

3. Multistatement Table-valued Functions
Syntax :
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE <> [ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_order
(
@customerid nchar(10)
) RETURNS @order TABLE (contactname varchar(60) ,orderid int, orderdate datetime
, requireddate datetime )
as
begin
declare @contactname varchar(60)

select @contactname = contactname
from customers where customerid = @customerid

insert @order
select @contactname , orderid, orderdate, requireddate
from orders
where customerid = @customerid

RETURN
end

example

select * from dbo.udf_order('alfki')

multistatement table-valued used if you need more logical programming to manipulate the data before displaying it.

Each type of UDFs has their own unique function depends on your needs in your project. But surely, it's very helpfull in your project later.

After this, i will try to collect some logic and make it as a function that may usefull for you.

Have a try and good luck.

Set nocount on

Anyone know what is "set nocount on" ?
Its function to set the message from query result to off.

example:

select * from tabelA

Usually, come out the message besides the results, such as below:

(xx row(s) affected)

You use "Set nocount on" to make the message disappears. Depends on your needs. Sometimes, you use it to decrease the server peformance, that after displaying the result of the query, it may give you the message around the query results. For your concern, the error message still be display.

Base on my experience, I use "set nocount on" in my Store Procedure.The purpose is to lighten my Store Procedure performance that it decreased the server works to give the message to its caller.

Can you imagine when your Store Procedure need to insert a lots of records one by one. It may result one message every success inserted record. Plus, other message if you have select queyr in your Store Procedure.

Have a try and i hope it will useful for you.

Have a nice day

Left vs Right Join in SQL Language

We know Left and Right Join in Sql Server. Do you know the differences between them ??
I will try to explain the difference between them and the tips from me in the usage.

Left Join, it use to join between 2 table which the left one will be display all records even there is no match record with the right one. Record with no match field partner will be display Null

The same for Right Join, we use it to join between 2 table and the right one will be display all records even there is no match record with the left one. Record with no match field partner will be display Null.

Sound difficult to understand with out the sample do you ?

Example:
Table A comes with Nim and Nama field ( have 3 records )
Tablel B comes with Nim and NamaMatakuliah ( have 5 records )

Select * from A left join B on a.nim=b.nim

The the results are minimum have 3 records plus the match record with table A

but when we change it into : Select * from A Right join B on a.nim=b.nim

then the results are minimum have 5 records plus the match record with table B.

Tips from me:
- Usually people much easier to compare between 2 table from left to right, So its better to use Left join rather than Right join.
- Before you create the query statement, make sure that the left table is the most records or it is the one that you want to keep it display the records even there is no match records in the join statement.

How is it ?There's a little bit understanding from my explain ?? Have a try, and ask if you still confuse about it.

Have a nice day.

A meaning of Failure

Today, suddenly I would like to share about what failure means from my point of view.

In this life, we experience failures. However, do we know what failure means. A failure doesn't mean hope vanishes. Sometime in failure we found new hope. For example, a few centuries ago, before science about chemistry is found, a lot of scientist try to change the structure of various object into gold. With varieties of experiment, starting from burning, spells, meditation, etc. However, some of them really try scientifically. Although experiencing failures, they begin to understand, that on earth every object is created from various kind of compound that might be organic or inorganic. From which we more familiar as element chart we studied in chemistry.

That is only one example. Of course we need to see from the positive side, which is the firmness in achieving one's purpose. Is there anything we can study from that failure. How to fix is what we call continuous improvement.

So guys, what do you think about what failure means? Does it gives you spirit to be a better person? Please look into the mirror, me myself always look into the mirror from every failure I experienced.

Have a nice day.