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.
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.
Next I’m going to create a table called “Customer” to store my data. Bellow is the query.
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.
At the end, I get and output like this.
This way you can create identity column(Surrogate Key) in a U-SQL table.