Tuesday, November 22, 2011

SQL 2005 - Bulk insert from Text File and Global Temp Tables

1. Introduction

In this article, we will look at some more techniques of inserting data. First, we will explore the usage of the "Temp tables". Then we will look at the "bulk insert using the text files".

2. The Temp Table

As the name suggests the table is temporary and it will get deleted after the usage. SQL developer uses a temp table to store some temporary result in it and query them later. Say for example, if your computation involves data from 6 or 7 tables, apart from other techniques Temp table also one nice technique to make calculations and store it temporarily for later use in that session.

To create the temp table, use the following syntax Example:
Select <Column_list> into #<Temp_Table_name> From <OriginalTableName>

In the above syntax:
Column_List: The list of the column from the main table. You can use comma separated column names or * for all columns
#<Temp_Table_name>: Name of the temporary table. Ex: #MyTemp
 <OriginalTableName>: Name of the original table from which the data is retrieved

Below is the example screen shot for the usage of the temp table. The NorthWnd database is used in two different query window.

Query Session 1

Fig 1. Temp Table at session 1



Query Session 2

Fig 2. Temp table at session 2


We have two queries windows shown in screen shots Fig1 and Fig2. In one query window we are filtering the employees from Seattle and in another window we are filtering the employees from London. The information retrieved is stored in the #EmpTemp temp table. The table with the # sign is known as "Temporary Table". In our example, two temporary table with name #EmpTemp is created for the query session 1 and 2. The two query sessions are shown in the above screenshots with red and green boxes.

The temp table will get deleted automatically when the Query window is closed. That means, the scope of the temporary table is until the end of connected session. And note that each query window maintains a separate connection to the database. This is why you get an error (Stating temp table already exists) when you try to execute the query shown above again in the same query window. 

Now If you look, I used same #EmpTemp in the second query window (Marked as green). Does it effects the content of the #EmpTemp created in the query window marked in red? No. Because, the temp table scope is session based and SQL see both the temporary table as two different instances even though they have the same name.

Note that you can also use create table query to create the temporary table and then insert the rows inside it.

3. Global Temp Table

Like temp table, you can create the "Global Temp Table" using the ## token before the table name. Right. What is global temp table? The global table is one, which is visible to all the active sessions. Let us say there are 12 users (Or connection. Remember it includes client code connecting to the database) when the global table is created.  Now, this global temp table is available for all 12 users or connected sessions. Now consider the situation below that will make you understand this:

1) All 12 Users active.
The Global table is available for all the 12 users

2) 3 Users disconnected and a new user, say B is connected to SQL Server.
The Global table is still available for remaining 9 Users as well as the newly connected user/Connection B

3) All users disconnected except B
The Global table is still available as the user B is active. Note that this user established a session when the global table is alive. That means he is also possibly using it. So SQL still allows Global temp table to be alive.

4) User/Connection B is also terminated and there is no user at present.
The global temporary table is deleted and it is no longer available.

Below is the usage of the Global temp table and note the ##sign before the table name.

Fig 3. Global Temp Table Example


The QLQuery4 window is making a connection to SQL Server and the “into ##” statement is creating the global temporary table. Now, I opened one more Query window QLQuery5 and this will create one more connection to the SQL server. Then the global table created is accessed in this new session:

Fig 4. Accessing Global Temp table from a dif


It proves that the Global table is accessible by other connection also. Think like this; the second query window (QLQuery5) is opened on a different machine and the global table is accessible there as well without any problem. Also, note that the Global Table also temporary and it does not belong to any schema.  You can access this table when you are in NorthWnd or Pubs or master. It does not matter.

Now close all the SQL Window (I am assuming that you do not have any client application/network users still maintains a connection to the server). Execute the query shown in the QLQuery5 by opening a new session. What happens? Object does not exist, right?


  1. Once you close the entire query window, the global temp table ##EmpTemp is deleted (In our case)
  2. When you opened a new query window, a new session is created and the Global table becomes no more for it.

4. Bulk insert from text file


As the title suggested we are going to see how do we insert a bulk number of records from a text file to an SQL Server table. The technique is; we should have the records in the text file in such a way that columns and rows of records are terminated by unique letters. We call these letters as "Field Terminator" and "Row Terminator". We will experiment this with a quick walk through.


1) Connect to the NorthWnd database. (You can download the T-SQL for it from SQL 2005 Page of this web-site)
2) Then Query the product table as shown below:

Fig 5. Prepare Text file content for Bulk Insert


3) Next, Click on the No Column name to select the entire row under this single column.
4) Then, Right click and copy the selected rows and paste it to a notepad.
5) Save it as Products.txt in D:\

The text file now has the data for the bulk insert explanation. Note that the field terminator is ‘~’ and row terminator is new line character ‘\n’. Now we will create a table that will consume the data from this text file.

6) Create a table as shown below in the NorthWnd DB

Create table BLKInsertTest(ProdId int, Prodname varchar(40), UnitMeasure varchar(21));

7) Run the below Query to insert the data from the text file to the table created in the previous step

Fig 6. Bulk insert from text file - Example

Here,

1: Specifies the name of the file from which we are going to pull the data for the table BLKInsertTest.
2: Field terminator character that tells where each column value ends in the text file for each row.
3: Row Terminator tells what is the row terminator. In our example new line is the row terminator.

Note that executing the query inserts 77 rows at once.

See U in the Next article.

Saturday, November 19, 2011

SQL 2005 - How do I use various Constraints in a database?

1. Introduction

"Constraints" are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:

  1. Primary Key Constraint
  2. Foreign Key Constraint
  3. Not Null Constraint
  4. Unique constraint
  5. Default Constraint
  6. Check Constraint

2. First Create two tables

To explain all these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window then execute it.

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);
Go

Note that there are no constraints at present on these tables. We will add the constraints one by one.

3. Primary Key Constraint


A table column with "Primary Key" constraint is called as the "key column" for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Student table as a primary key. Follow these steps:


  1. Right click the student table and click on the modify button
  2. From the displayed layout select the StudId row by clicking the Small Square like button on the left side of the row.
  3. Click on the "Set Primary Key" toolbar button to set the StudId column as a primary key column.
Fig 1. Setting key column for the Table
Now StudId column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. "Multiple columns" can also participate on the primary key and in that we call that as "Composite Primary Key". When the key is composite, the uniqueness is considered among all the participant columns by combining their values.

4) Not Null Constraint


The "Not Null" constraint is useful to stop storing the null entries in the specified columns. We will mark student name column as a not null column. This allows us always having some entries in the student name column of the student table without having NULL. Follow the steps below:


  1. As you did previously, bring up the table design view by clicking the modify context menu for the table.
  2. Remove the check mark as shown in the below picture. This action will enable the Not Null constraint for the StudName column.

Fig 2. Setting Not Null Constraint

5. Default Constraint

"Default" constraint allows you set a default value for the column. That means when a row is created for the first time, and there is no entry specified for the column that has default constraint set, then the default value is stored in the column. Note that this is not a Not Null constraint and do not confuse the default value always enters the column disallowing the Null entries. The default value for the column is set only when the row is created for the first time and column value is ignored on the Insert. Modification to the column with NULL value  or even the Insert operation specifying the Null value for the column is allowed.

Let us set the Default value of 1 for the Class column of Student table. Follow these steps:

  1. Bring up the table designer
  2. Select the Class Row as you already did.
  3. At the bottom of the layout, you will see a Column properties as shown in the below picture. Set the default as shown below:
Fig 3. Setting default constraint

6. Unique constraint

A "Unique Constraint" does not allow the duplicate values. But, the column can have multiple Null values. For our example, we do not require any unique constraints. Follow the below example if you want to set a unique constraint for a column:

ALTER TABLE Student ADD CONSTRAINT UQ_CONSName UNIQUE (StudName)

The above T-SQL sets a unique constraint for the column StudName. UQ_CONSName is the name given to the constraint. It will be useful if we want to delete the constraint later.

7. Check Constraints


"Check Constraints" are custom rules that can be applied on the column to take the decision of value is allowed or not. Let us set some check constraint for the TotalMarks column of the Student table. The data can be stored in this column only when the data is greater than zero or less than 1200.

1) First, set the not null constraint for both the columns in the table.

Fig 4. Set Not Null


2) Expand the TotalMarks table and right click the Constraints folder. From the displayed context menu select the "New Constraint"

Fig 5. Add new check constraint


3) From the displayed check constraints dialog, we set the above said check rules for the column. To do that, type the expression for the column TotalMarks in the Expression field. The expression is: (TotalMarks < 1201) AND (TotalMarks > 0)

Fig 6. Add check condition for check constraint


4) Change the name of the constraint to CK_TotalMarks. Then click the close button. Refresh the Constraints folder by selecting the refresh from the context menu of the Constraints folder. The added constraints with the name is shown below:

Fig 7. Check constraint in the database object tree


Now the Total Marks column does not allow the negative marks or marks more than 1200.

8. Foreign Key Constraint

"Foreign key" constraints are useful for maintaining the relationship between two tables. If a column allows the values only when the value exists on the column of some other table then the column is said to have the foreign key.  A foreign key should refer primary key or unique key column of some other tables.

In our example, we will create a foreign key for the column StudentId of the TotalMarks table. Let me introduce the Database diagram to do this task. You can play around it as the diagrams are not only to show the existing database relationship, they exist actually to design your database in an easy way. You can change the data types, add relationships (That we are going to see now), introduce constraints etc..

Follow these steps to create the Foreign Key constraint using the database diagram [The easy way]:
1) Expand the database in which you created the two tables for this example. Then Right click the Database diagram and choose "New Database Diagram"

Fig 8. New database diagram


2) In the add table dialog select both the tables. If you use a different database then select the tables that we created for this example. Then click the Add button. Click the close button to dismiss the dialog.

Fig 9. Add Tables to database diagram


3) You will see the following two tables on the design surface

Fig 10. Selected tables in the database diagram


4) Now hold down the left mouse button on the StudId and drag & drop the mouse pointer on the StudentId. This will create a foreign key in the Total Marks table.

Fig 11. Add Table relationship using database diagram


5) Accept the default  by clicking the OK button from the displayed dialog after reviewing the details it displayed

Fig 12. Set Name for Foreign Key


6) The database diagram now shows the relationship between the tables

Fig 13. Table relationship as shown in the database diagram


Note: All the Steps are done using the SQL Server 2005. I am stopping it here. You can go ahead and examine each constraint by inserting some sample data to the tables.



Tuesday, November 01, 2011

[ Dot Net Remoting ] - Accessing Http Remote objects using SoapSud Generated Metadata

1. Introduction

In the previous examples, we used "TCP Channel" to communicate the remote object in the server machines. Also, we have the reference to the server project in the client development projects.  Giving the code to the client is not advisable as the client can go ahead and strip the given to know some of the implementation details.

In this article, we will look at how do we use the "Http channel" for communication and how do we create the Metadata proxy from the server deployed remote objects using the "SoapSuds" command line utility. OK. Let us start. The explanation is reduced here. Read my first article on Dot.Net remoting link for basic details on remoting.

2. The Http remote Server

1) The server is a C# console application. It has a simple remote object called RServer. Please note that the namespace name also RServer. It is not good having namespace and class name in the same name. So I would recommend separating the class name from the Server namespace. As usual, our RServer class is derived from the MarshalByRefObject class to make it a remote object.  Below is the code for it:

//Server 01: Required Declarations
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Http;

//Server 02: Http based Server
class RServer : MarshalByRefObject
{
    public void TestObject()
    {
        Console.WriteLine("Object Test. Test Object Function called" + Environment.NewLine);
    }
}

In the constructor, we are just printing some message so that we can ensure whether server object is created or not just by looking at the console window of the server. Also, note that this time we are going to use the HTTP channel and hence included the channels.http (using System.Runtime.Remoting.Channels.Http;)


2) In the server application main, we are creating http channel and then registering the remote object as a SingleCall object. As the basic example (First article on DotNet remoting) has all the required explanation, I am skipping those repeated details here. Below is the code:

static void Main(string[] args)
{
    //Server 03: Create a http channel for soap and register it.
    HttpChannel httpchnl = new HttpChannel(7212);
    ChannelServices.RegisterChannel(httpchnl, false);

    // Server 04: Register the server object
    RemotingConfiguration.RegisterWellKnownServiceType(typeof(RServer), "RServer",
        WellKnownObjectMode.SingleCall);

    // Server 05: Make the server running
    Console.WriteLine("Server Started" + Environment.NewLine);
    Console.ReadLine();
}

3. Generating MetaData dll to share with client

Once the server is ready we are all set to go ahead and create the Metadata assembly that can be shared with the client piece code. Remember in the previous example when I used the TCP communication channel, I usually add whole server project as the reference. The alternate technique to that is having the declaration in a separate assembly (dll) and shipping that to the client. Here we are going to generate separate metadata dll for our server and going to ship that dll to the client.

To generate the Metadata dll, first, run the server project or Exe. When the server is in the running state, launch the Dot net command prompt and access the "SoapSuds" utility. Below is the option switches used to generate the metadata assembly for our server example:

SoapSuds –oa:<OutputName>.dll –Url:http://Localhost:<PortNo>/<RemoteObject>?Wsdl

In the above command,
oa - option specified the output path and name of the dll
-URL – option specifies the http url to the remote object for which we want to create the meta data dll

Creating the DLL is shown in the below video.

Video:

4. Consuming the metadata dll in the client

The client is also Visual C# console application. Once the client project is created, after giving the reference to dotnet remoting, the reference to the metadata dll is given using the browse tab of the add reference dialog box. Once the reference is given, we have access to the remote object and we can create it using the new operator.

Getting the reference to the metadata dll in the client project is shown in the below video.

Video:


5. Accessing the Remote object through Http

Once you created the reference to the Meta dll formed on the server machine using soapsuds, you can simply access the remote object using the new operator just like you create normal objects. Below is the explanation for the code:

1) RServer is the namespace in the server. Note that in the server implementation the Remote class name, as well as the namespace name both are same.


//Client 01: Use the Meta data dll.
using RServer;

2) The remote object is created using the new operator. But, in the background, through the Meta data DLL, we are making a call to the wrapped assembly to get the proxy. This proxy object actually calls the real object at the server end. And it knows the communication protocol, server address and port number of the communication.


//Client 02: As we have proxy to the remote as wrapped meta data, you can use new oprator
//              to create the object
Console.WriteLine("Creating the Instance of Remote object\n");
RServer.RServer RemoteObj = new RServer.RServer();

3) The remaining lines of code are simple as it just makes a call to the function exposed by the above-created object. In user perspective, it is just an object. But, the function call is executed on the server. You can observe that by looking at the server machine’s console window. Below is the piece of code that does not require much explanation:


//Client 03: Calling the Remote method
Console.WriteLine("Press any key to Make a call to Remote function\n");
Console.ReadLine();
RemoteObj.TestObject();
Console.WriteLine("Press any key to Close");
Console.ReadLine();

Video


Source Code : Download
Note: The sample is created using the VS2005 IDE. 
Like this site? Tell it to your Firend :)