Feeds:
Posts
Comments

Archive for October, 2009

1.Export Table Output as XML/Generate XML from Table

select * from [your_table_name] for Xml AUTO,elements,root(‘root_name’)

select * from [your_table_name] for Xml RAW,elements,root(‘root_name’)

Save the output as xml

2.Import XML File into SQL Server using SSIS

Open BIDS

Create a new Project/Package

Go to Data Flow Task drag one XML Source

Click on XML Source

Browse XML Source Location where you saved your xml file above

Click on Generate XSD and save this file to a location

Drag one OLE DB Destination Component and create connection to destination SQL Server Instance.

And Finally Map both

Run Package

Bye

Advertisements

Read Full Post »

Create a table for this example

 

create table
Employee

(

emp_id int ,

dept_id varchar(20) ,

no_of_leave int

)

 

 

 

 

·        
Populate this table

 

 

declare @empid int

 

declare @noofleave int

 

set @empid=1

 

set @noofleave=0

 

while 
@empid
<11

 

begin

 

insert into
Employee

 

values (@empid,‘A’,@noofleave)

 

set @empid=@empid+1

 

set @noofleave=@noofleave+5

 

end

 

 

GO

 

declare @empid int

 

declare @noofleave int

 

set @empid=20

 

set @noofleave=0

 

while 
@empid
<31

 

begin

 

insert into
Employee

 

values (@empid,‘B’,@noofleave)

 

set @empid=@empid+1

 

set @noofleave=@noofleave+3

 

end

 

GO

 

 

 

declare
@empid 
int

 

declare
@noofleave 
int

 

set
@empid
=40

 

set
@noofleave
=0

 

while 
@empid
<51

 

begin

 

insert
into Employee

 

values
(@empid,‘C’,@noofleave)

 

set
@empid
=@empid+1

 

set
@noofleave
=@noofleave+7

 

end

 

GO

 

·        
Basic Pivot

 

 

 

select *
FROM Employee

 

pivot (sum(no_of_leave) for emp_id in ([1],[21])) pvt

 

 

 

 

·        
Dynamic Pivot

 

 

 

DECLARE @PivotColumn VARCHAR(MAX)

 

SELECT @PivotColumn =

 

COALESCE(

 

@PivotColumn + ‘,[‘ + cast(emp_id as varchar) + ‘]‘,

 

‘[‘ +
cast(emp_id as varchar)+ ‘]‘

 

)

 

FROM (

 

select distinct
emp_id 
from Employee)b

 

 

 

DECLARE @PivotSQL NVARCHAR(MAX)

 

SET @PivotSQL = N

 

SELECT *

 

FROM (

 

select
emp_id,dept_id,no_of_leave FROM Employee

 

) AS
PivotData

 

PIVOT (

 

SUM(no_of_leave)

 

FOR emp_id
IN (

 

 +
@PivotColumn 
+ 

 

)

 

) AS
PivotTable

 

 

 

 

EXECUTE(@PivotSQL)

 

 

Read Full Post »

For Text File

C:\Windows\system32>SQLCMD -S your_server_name -U your_user_namae  -P your_pasword -d your_database_name  -Q “SELECT * FROM table_name” -o “C:\Users\abc\Desktop\output.txt”

For csv File

C:\Windows\system32>SQLCMD -S your_server_name -U your_user_namae  -P your_pasword -d your_database_name  -Q “SELECT * FROM table_name” -o “C:\Users\abc\Desktop\output.csv”

Read Full Post »

For Newbies, I suggest to go through Different Types of Engine Supported by MySQL

Please Visit:http://articles.techrepublic.com.com/5100-10878_11-1058872.html

MyISAM is MySQL’s extended ISAM format and default database engine but it doesn’t support transactions .

So, Consisder an example by creating a table of EngineType Innodb that supports transactions .

Go to Command Line connect to your MySQL Instance(I have used command Line but you can use Your Front End Like MySQL Query Browser or Any Other)

1.Connect to MySQL

C:\Windows\system32>mysql -u root -P3306

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.22-rc-community-log

MySQL Community Server (GPL) Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

2.Create database test

mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.00 sec)

3.Select Database that you have Created

mysql> use test;

Database changed

4.Create a table with Engine Innodb

mysql> create table NNN(AA INT) -> ENGINE=INNODB;

Query OK, 0 rows affected (0.05 sec)

5.Check whether Table is Successfully Created using Engine Innodb

mysql> show create table NNN;

+——-+———————————————————————- ——————+ | Table | Create Table | +——-+———————————————————————- ——————+ | NNN | CREATE TABLE `nnn` ( `AA` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +——-+———————————————————————- ——————+ 1 row in set (0.00 sec)

So,Above we can see Table tn created with Engine Type Innodb

6.Start Transaction

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

7.Insert Values in Your table

mysql> INSERT INTO NNN VALUES(12);

Query OK, 1 row affected (0.00 sec)

8.Rollback Your Transaction

mysql>ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

9.Check the Output

mysql> SELECT * FROM NNN;

Empty set (0.00 sec)

So, We can see that table dont have a single record because transaction is Rolled Back

10.Start Another Transaction

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

11.Insert value

mysql> INSERT INTO NNN VALUES(12);

Query OK, 1 row affected (0.00 sec)

12.Commit Transaction

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

13.Check Value in table

mysql> SELECT * FROM NNN;

+——+ | AA | +——+ | 12 | +——+ 1 row in set (0.00 sec)

Value reflected in table NNN

Happy Transactions!!!

Read Full Post »

Open BIDS.

Create a new integration service project.

Go to Control Flow Tab and right click on window.

Then, go to variables

Add three varaibles namely input_1,input_2 and Name with data type int32,int32 and string respectively.

Add Parameter’s default values 10 to each input_1 and input_2 where as ‘amit’ to variable Name.

Add Execute Script Task to Control Flow

Click on Script Task/Edit, Go to General tab and add read only variables that we have defined above.

Click on Edit Script and write the code as provide below

Dim input1 As Integer = CType(Dts.Variables(“input_1”).Value, Integer)

Dim input2 As Integer = CType(Dts.Variables(“input_2”).Value, Integer)

MsgBox(“Hi “ & Dts.Variables(“Name”).Value.ToString() & Environment.NewLine _

& “Multiplication of two numbers provided is :” & (input1 * input2).ToString() & Environment.NewLine _

& “Bye”)

Execution with Default Parameters:

Go to Command Prompt and browse till C:\Program Files\Microsoft SQL Server\100\DTS\Binn

(Do check your Installation  Directory of Micrsoft SQL Server and Configure your Path accordingly)

and Then

DTExec.exe /f  “your_path_to_dtx_package”

In my case it will be

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx”

So, your command should look like below

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx”

And Run the above

Execution With  Parameters:

Now, after executing Package with default parameters give your own parameters and excute

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx” /SET \Package.Variables[User::input_1].Properties[Value];”29″ /SET \Package.Variables[User::input_2].Properties[Value];”29″ /SET \Package.Variables[User::Name].Properties[Value];”Singh”

And Run the above

Read Full Post »

View can be defined as virtual table created from one or more actual tables underlying in databases.

As mentioned above “virtual table” that means database wont store the data for view instead of this it only stores a definition of the view.

** When I say “only stores a definition of the view” then I am only referring to simple view there

Is exception to that we will discuss later!

Coming back, so when we try to fetch data using a view database engine fetches the data from actual tables using DEFINITIONprovided for particular view

Why views?

A) To hide complexity of database schema from a end users (To make their life simpler)

B) To provide restricted access (Selected columns from a table NOT ALL) to users

Create a simple View

CREATE VIEW vwStudents
AS select Student_Name,Phone,Address

From Students

So, we can see only selected columns from students table are part of vwStudents and not all columns.

Use View

Select * from vwStudents

Indexed Views in SQL Server / Materialized Views in Oracle:

Why Indexed Views/ Materialized Views?

As I have mentioned above when data is requested by using a view, database engine dynamically pulls data from actual tables based on view definition. If data to computed dynamically is too large in size will result in poor performance.

So, rather than dynamically computing if we refresh our view when data in actual tables gets updated!!!

That is why Indexed Views/ Materialized Views.

Here is exception that I was talking point earlier.

An indexed view is a view that has a unique clustered index created on it.so, here row exist as row in database and no longer only definition that is kept!!!

Read Full Post »

%d bloggers like this: