BCP: export data to azure database

BCP (bulk copy program) is a great way to transfer huge data (millions of rows) to an Azure database. I found that it is very efficient in terms of ease of use and speedy with which the data could be transferred. The bcp utility is a command line utility that ships with Microsoft SQL Server. It bulk copies data between SQL Azure (or SQL Server) and a data file in a user-specified format. The bcp utility that ships with SQL Server 2008 R2 is fully supported by SQL Azure.

Following are the steps to use this utility to export the data to a flat file and then exporting the data to the Azure database:

• Copy data from customer table into a temp table
  • select * into [temp_employee] from employee
• Copy data to local file from a table in a local database.
  • BCP [server_name].[database_name].[schema_name].[temp_employee] OUT c:\temp\employee.txt -T –c
• Create the temp table in the destination database in the cloud
• Copy data into the azure database.
  • BCP [azure_database_name].[schema_name].[employee] IN c:\temp\employee.txt -S servername.database.windows.net; -U <username>@<servername> -P <password>

Following is a sample of the Bulk copy output form the command window for reference. The most interesting aspect to observer is that data transfer rate stood at 5206 rows per second for this particular attempt. I believe that higher transfers could be a possibility depending on the available network bandwidth.

Exporting Data out of SQL Azure

Data can be retrieved in a a huge set from the in SQL Azure by running the following statement at the Windows command prompt:

bcp [database_name].[schema_name].[table_name] out C:\temp\[file_name].txt -c -U username@servername -S tcp:servername.database.windows.net -P password

References:

1. BCP and SQL Azure
2. BCP Utility

SQL Server: How to select data from executing dynamic SQL

In his post I will show you how to select data from executing dynamic SQL. This method is handy in a scenario where you plan to create a Function Import (using EntityFramework [2]) for a stored procedure that returns a result set based on the execution of a dynamic SQL. Capturing the result in a temp table or a table variable will allow EF to define the return type of the Function Import. In my example I am using table variables as they are easier to maintain than temp tables which have a physical presence. The following code returns one row of employee details that was insert into the table variable using dynamic SQL. Dynamic SQL can be executed using exec [3] statement or the sp_exectuesql system stored procedure.

declare @employee table(id int, name nvarchar(50))
declare @str nvarchar(1000) = 'select 1, ''Joe'''

insert into @employee(id, name)
exec(@str)

select * from @employee

Here is another way to capture the output where sp_executesql is used to execute a dynamic sql string. The following code snippet returns the total numbers View in the database from the sys.objects object catalog view [4].

Declare @strSQL as  nvarchar(100)
Declare @strParam as nvarchar(100)
Declare @XType as nvarchar(2) = 'V'
Declare @rtnCount as varchar(100)

Set @strSQL = 'Select @ObjCount=count(*) from Sysobjects Where xType=@xType'
Set @strParam = '@ObjCount int output, @xType nvarchar(2)'
execute sp_executesql @strSQL, @strParam, @rtnCount  output ,@xType

Select @rtnCount [View Count]

References:
1. sp_executesql (Transact-SQL)
2. Entity Framework
3. EXECUTE (Transact-SQL)
4. Object Catalog Views (Transact-SQL)

Sql Server: Temp table vs table variable

Temp table vs table variable. One question that lingers in my mind is; how to decide between using one over the other?  I will present some points that I have gathered that could aid in helping you make a decision in picking one over the other:

1. Reseed the index of a table variable is not allowed.

2. We will not be able to explicitly create Indexes on a table variable.

3. Altering the schema of a table variable is not possible

4. Table variables do not have to drop it after usage, they will be automatically cleaned up when they are out of scope.

5. Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. They are not affected by transaction rollbacks.

6. Table variables contribute to more efficient Query Processing.

7. CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

8. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK.

Table variables will offer best performance when the data size is small and the . The next time you have to make a choice, choose wisely between the two and make a wise choice for the usage to derive optimal performance from your queries.

References:
1. Table (Transact SQL) – Microsoft SQL Server

SQL Server: Case sensitive string comparison

Here is a useful tidbit. We can use one of the following two methods to perform a case sensitive string/field comparison when a SQL Server database does not support case sensitivity.

declare @a as nvarchar(20) = 'Test'
declare @b as nvarchar(20) = 'TEST'

--Method1:
if(convert(varbinary,@a) = convert(varbinary,@b))
   select 'identical'
else
   select 'non-identical'

--Method 2:
if(@a = @b COLLATE Latin1_General_CS_AS)
   select 'identical'
else
   select 'non-identical'

SQL Server: Data transfer for Excel using Linked Server

Introduction

I will demonstrate yet another way to perform data transfer to and from an excel file using a Linked Server in SQL Server 2008. In this demo I will show you how to:

• Create a Linked Server using Excel as the data server
• Import data to a SQL Server table from a spreadsheet
• Export data to a spreadsheet from a SQL Server table

Create a Data Source

As the first step in this process I have created an Excel file with a spreadsheet named “Employee”, followed by defining the headers and inserting 5 records as showing in following illustration:

Create a Linked Server

Now that we have a data source let us create a linked server using an the Excel file created in the previous step as the data source. Open SQL Server Management Studion (SSMS) and expand “Server Objects” under the intended SQL Server, to find the Linked Servers item.Right click on Linked Servers and click on “New Linked Server” to see following dialog box. EXCEL_LINKED_SERVER is the name that I have chosen to call this linked server. Then we need to populate the Provider, Product Name, Data Source and Provider String like in this example and click the OK button.

A linked server should have been created successfully at this time. Right click on the “Linked Server” tree view item and click on refresh to see the newly created linked server. Expand the linked server to see the Exployee$ spreadsheet under the list of tables.

Import Data

At this point you should be able to access the Excel spread sheet just like any other SQL Server database table.  Let us import data into a table named DataFeed in the Demo database. It is important to note that the spreadsheet name in the SQL query has to be accessed only by preceding spreadsheet name  with “…”  Only one dot is used to represent the current database, two dots to represent another database in the SQL Server instance and three dots represent a Linked Server. When we would have ran the following SQL query, a table named “DataFeed” would have been created and 5 records from this table would be displayed in the results pane.

Export Data

Let us insert a couple of records into the DataFeed table that was created in the above step, followed by exporting those two rows to the spreadsheet. When the following query us run, you will observe that the spread sheet will have 7 records.

Now Let us look at the new data in the spreadsheet: