SQL Server Create Table

How to create table in SQL Server that very simple for the beginner.

Note!
Please do not create the same  name of Table in View Module of SQL Server Database

CREATE TABLE [dbo].[DATABASE_TABLE1](
[T_YEAR] [varchar](50) NULL,
[T_MONTH] [varchar](50) NULL,
[T_ACCOUNT] [varchar](50) NULL,
[T_NAME] [varchar](50) NULL,
[T_LASTNAME] [varchar](50) NULL,
[T_SALARY] [decimal](18, 2) NULL
) ON [PRIMARY]

Create command as details as above then click on "Execute"
or You can follow methods as image below

STEP-1Create command to create table





STEP-2After click on "Execute" then click right on database for refresh, you will see the table have created




STEP-3Click right at table then select design menu



STEP-4
You will see the structure of table as image below



STEP-5
Set Primary Key by click right on that field

Primary Key have created

Read More

SQL Select change column to row

How to change column from table to row?

Example data from table1

id	name	lastname	month1	month2	month3
1	name1	lastname1	300	400	500
2	name2	lastname2	10	20	30

Code :

SELECT id,name,lastname,amount,month
FROM (
SELECT id,name,lastname, month1 as amount, '1' as month FROM table1 as t1
UNION ALL
SELECT id,name,lastname,month2 as amount, '2' as month FROM table1 as t2
UNION ALL
SELECT id,name,lastname,month3 as amount, '3' as month FROM table1 as t3
)

Result :

id	name	lastname	amount	month
1	name1	lastname1	300	1
1	name1	lastname1	400	2
1	name1	lastname1	500	3
2	name2	lastname2	10	1
2	name2	lastname2	20	2
2	name2	lastname2	30	3

Read More

Import File System to SQL Integration Service Catalogs for SQL Sever Agenda

Before you will set schedule on SQL Server Agent, you have to import file package from SSIS to Integration Service first. OKEY! it's time to go.

STEP-1

Go to  SQL Server Studio 2014 -> Server type : Integration Services -> fill "Server Name or IP" Server

STEP-2

Click on "Store Package" you can create new folder by click right and "New Folder" for collect your project jobs

STEP-3

After you created folder to keep your package, you have to import package from SSIS to folder by click right on folder you have just created then select "Import Package"

STEP-4

Select package as your job path then click on "OK" to finished

Next Step to create job schedule on SQL Server Agent
http://www.mymemoboxs.com/2015/02/create-agenda-on-sql-server-agent-for.html

Notice :

If you changed any thing on your SSIS package, you have to reimport that package again, otherwise after you create job schedule and click on run, you may see many error.

GOOD LUCK 

Read More

Create Agenda on SQL Server Agent for Run Script on SSIS

After you create all SSIS to transfer data from any platform to SQL server, now you have to set up schedule for run script as automatic by use SQL Server Agent .

At first you have to import your package to Integration Services as details here
http://www.mymemoboxs.com/2015/02/import-file-system-to-sql-integration.html

STEP-1
Open SQL Server, click on right "Jobs" at SQL Server Agent then hit on "New Job"

STEP-2

Dialogue will appear as image blow, you have to set the on "General" section
Name : "Run Company Profile" or any name as you wanted
Owner : select administrator permission by click on browse
Category : pick up on "Data collector"

STEP-3
To select source file click on "Steps"
Step name : set the process name as you wanted
Type : select SQL Server Integration Service Package
Run as : SQL Server Agent Service Account will automatic set
Package sourve : SSIS Package Store
Server : localhost or IP Server
Package : Browse for your package job

STEP-4
Click on "Execution Options" and click on "Use 32 runtime" then click Ok to finished create source package

STEP-5
To set the time to running script project by click on "Schedules" on the left menu

STEP-6To run process, click right on job schedule and select "Start job as Step"

Read More

Import data from Excel to SQL by SSIS

How to import data from excel to SQL server
The concept seem like import data from other format but you have to prepare your excel file in correct data.

Merry X'Mas and Happy New Year 2015
Wish all of you get lucky and happy life all of the year.










STEP-1
Prepare your excel file data in correct format.

STEP-2
Create new package and drag and drop "Data Flow Task" on Control Flow page

STEP-3
Double click on "Data Flow Task" box, then drag and drop "Flat File Source" then double click on "Flat File Source" box
 then you will see the popup for configuration as image below

STEP-4
Click on "Browse" button to select excel data source file as the part you saved on computer

STEP-5
Click on "Column" at the left menu for check fields on excel file, after that click on "OK"

STEP-6
Drag and drop tool on menu left is "OLE DB Destination" then select connection (you can see here how to connect to database)
and select table destination

STEP-7
Click on "Mapping" on the menu left for check your data field, you have to select data to be match with destination

STEP-8
If you see the error after mapping data, please be sure that your data type, data length is the same as table destination
if not, you have to convert data (you can see how to convert data here)

NOTICE!
This is very important, if your excel file have empty value, you have to check and convert to zero install (in case that column is numberic)

Read More

SQL 2014, Set Primary Key, Remove Primary Key in Table

I still do not understand why I can not change data type of field and also if I would like to set "Allow Null" to be "Not Allow Null" then SQL Server stop my dream.

For set primary key in Table of SQL server that very easy, you just click right on the field and select "Set to be primary key" That's it!.

STEP-1
Click right on the field then select "Set Primary Key" but please be sure that field is not allow null and have no double data in field.

Or you can hard code by (Open New Query)
This can set multiple primary key in the same time (Please do not push in Execute ^_^)
ALTER TABLE tb_name ADD PRIMARY KEY (primarykey1,primarykey2)



STEP-2
After you have execute you have to refresh table and click right select "Design", then you will see now you already have primary key in your table.




How to remove Primary Key ?
Click right on the table and select "Design", you will see your fields and attributes of each field then you will see a key icon in front of field was primary key, you have to click right on that primary key field then select "Remove primary key" as image below.





Beware!
- If you want to set a field is primary key, must be sure that field is not allow null
- You have to be sure that field is unique data
- If you created field is not null before, you have to changed to null by use

ALTER TABLE tb_name ALTER COLUMN field_primary int not null


Special STEP Simple

Go to Tool -> Option -> Design then remove checked list as detail below
- Warn about different detection
- Warn about tables affected
- Prevent saving changes that require table re-creation

Good Luck and ENJOIN!

Read More

Store Procedure How to create on SQL Database?

Why must create store procedure? How it's work?
At the first time I really did't see how was store procedure help me, because I could create code or query by use SQL or MySQL format but after I know about procedure that sound pretty cool!

1. Safe performance of database because even if you have many jobs must query or updated in same time, store procedure can help and better then use query by an other code
2. Easy to use and create

OKEY! let's create

STEP-1Click right at "Store Procedure" menu in your database list, then go to "New" and click on "Store Procedure"

STEP-2
You will see procedure area to build the code. The system will generated default value as details below.

STEP-3
In line "CREATE PROCEDURE [dbo].[your_store_procedure_name]" you have to changed to your procedure name, Then click on "Execute" for create new procedure. After you refreshed your database, you will see the new procedure name appears as you just created.

STEP-4
Close window on STEP-3 and click right on "Procedure_your_name" and select "Modify" to programming.




STEP-5
A simple code to delete data in period as selected data.
Beware, Please arrange parameters were match with your selection data.
Download Example Code Here

Read More

Change Data Type in SQL Table

No No No!
I tried several times to changed my data type in table of SQL Server but it was always said "Could not save!"

I was wondered why? I ever have changed the data type in MySql table very simple by click on the field and select the data type as I wanted but for SQL Server I could not do like that.

However I have a solution ^_^

STEP-1
Click on "New Query"



STEP-2
Fill your code as details below but please change tb_name and field_name as your table data, nvachar(20) and int(10) they are new data type I wanted to change.

ALTER TABLE tb_name ALTER COLUMN field_name1 nvarchar(20)
ALTER TABLE tb_name ALTER COLUMN field_name2 int(10)

STEP-3
Click on "Execute" and now your data type field have changed to the new one as you needs ^_^

Special STEP Simple

Go to Tool -> Option -> Design then remove checked list as detail below
- Warn about different detection
- Warn about tables affected
- Prevent saving changes that require table re-creation

Read More

SSIS Convert Data From MySql to SQL (Thai Language and Other )

Oh! My God! If we do some thing in first time, It quite hard for us but after we started walking then running it was easy for us ^_^ do you agree with me? I spent about full day to find the solution to fixed my Thai alien language in database, Every things look perfect in MySql database but after I have transfer all Thai data to SQL server why it SUCK!!! (OOP! sorry I was rude but T_T it because my pressure for a day to searching solution) I am guarantee if you do all step as I suggested you will do not miss beside you do on wrong way ^_^...then go ahead!

My Thai Character before convert data in SQL DB

STEP-1
Set up your region as the language you are Click here for more details 

STEP-2
Be sure that you have to configuration character on ODBC is tis620 (For Thai language and for an other language please choose your) Click here for more details

STEP-3
SQL server are the same character set (For this step let's me skip please ^_^ but I guess it will not too difficult)

STEP-4
Open SSIS for develop to transfer data from MySql to SQL server then click on your job
As the image below I pick up ADO Net Source because my source database is MySql, I have tried choose an other one but that did't work.

STEP-5
Drag and Drop "Data Conversion" on your window job for convert type of data, Then double on box data conversion

STEP-6You will see the popup window for you to pick up the data as your want to convert, Then change the data, almost text from MySql have to convert to Unicode String and be sure in your SQL database have already set data type was "nvachar".

STEP-7
Map data to the new one (Double click on OLE DB Destination, then click on "Mappings") , After you have set convert data to the new one, SSIS will automatic create new field for you immediately like "copy to thainame", please see image below

STEP-8
Now LET'S GO!! run your process then you will see the magic ^_^, now you can read your language
Congratulation with you too!!

Read More

Character Setup for Transfer data from MySql to SQL Server

WHY!!!! my language it was an alien after I have transfer data from MySql database to SQL server? Yeah! I know that many developer was upset when it was not as we wished, I have set tis-620 at my MySql database and I also did the same on SQL server but WHY!?. OKEY! please clam down and I have a solution for you, Just do it carefully by step by step then I am guarantee that you will get the answer and I am sure for an other language it will be the same, you just need to change character as your needs.

STEP-1
Go to control panel on the window menus

STEP-2
Find "Region" menu but if you do not see please change your view to small icon

STEP-3
Then you will see the popup window as photo below then click on "Administrative" tab

STEP-4
Click on "Change System Locale...", Exactly the default will be "English" if you installed window and choose the language as English or depend on your choose, You have to selected language as you want (In image I have to selected Thai)

STEP-5

You almost be done! ^_^ just click "Ok, Ok and Ok" then Restart window but my Warning is please save all jobs before you have to restart, I confirmed that your Thai language or any language will be fine after you have transfer data from MySql to SQL Server if you miss that mean you were wrong XOXO ^__^ and please set ODBC to tis620 or character code as you needs too. Click here for setup ODBC Character

STEP-6
For more add-on, If you have to transfer data from MySql to SQL server by SSIS you have to set conversion data, Please see in next stop by click on here

Read More