How to add an identity column into a U-SQL table

Unlike SQL Server , U-SQL does not support Identity insert by default and for that we have to use alternative approach.

Let’s say you have a data file which consists of Customer information and you need to insert it into a U-SQL table. This is how my data file look in Excel.

image

As I need to insert these records into a U-SQL table, First I will create a U-SQL database called “IdentityInsert”.  Bellow is the query I use for database creation.

image

Next I’m going to create a table called “Customer” to store my data. Bellow is the query.

image

In the customer table, I have added a column called “CustomerKey” and I need this column to work as a primary key in my table .In data warehousing terminology, this field will work as the surrogate key of the “Customer”table. Additionally, I will have all the fields in the data file.

Now I ‘m going to extract data from Customer.txt file and insert those records into “Customer” table. Since there is nothing called Identity insert in U-SQL, I’m going to use Rank function in my query. However, Rank function returns a Big Integer(int64) type output and and my column is in int32 type. Therefore I need to perform conversion in here. Here is the query for insertion.

image

At the end, I get and output like this.

image

This way you can create identity column(Surrogate Key) in a U-SQL table.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s