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)