/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Thursday, February 12, 2015

SQL SELECT INTO Examples and Linked Server in SQL Server 2008

SQL SELECT INTO Examples

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

SELECT * INTO DBNAME.DBO.NEWTABLE FROM LINKEDSERVER.DBNAME.DBO.OLDTABLE
This will both create the new table with the same structure as the original one and copy the data over.

How to Create a Linked Server?


  1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
  3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
  4. In the New Linked Server dialog box, on the General page, in the Linked server text box, enter the full network name of the SQL Server you want to link to.
  5. Under Server type, click SQL Server.
  6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.
  7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.
  8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.
  9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the RPC and RPC Out parameters to True, and then click OK.

Note
This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server.

Copying data within a same server TheDestinationDB is theh current database.

USE TheDestinationDB;

SELECT * INTO NewTable FROM TheSourceDB.Schemaname.OldTable
Create a backup copy
Another Option with a single db server: SELECT * INTO NewTable FROM OldTable
Copy only a few columns into the new table:
SELECT CustomerName, ContactName INTO CustomersBackup2015 FROM Customers;
Copy data from more than one table into the new table:
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2015

FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just Add a WHERE clause that causes the query to return no data:

SELECT * INTO CustomerNew FROM dbo.Customer WHERE 1=0;
Copy only the 'India' customers into the new table:
SELECT * INTO tblIndiaCustomersBackup2015 FROM Customers WHERE Country='India';









Read More »

Wednesday, February 4, 2015

Entity Framework (EF) TransactionScope vs Database.BeginTransaction

Entity Framework (EF) TransactionScope vs Database.BeginTransaction

In today blog post we will talk a little about a new feature that is available on EF6+ related to Transactions.
Until now, when we had to use transaction we used ‘TransactionScope’. It works great and I would say that is something that is now in our blood.
using (var scope = new TransactionScope(TransactionScopeOption.Required)) 
            { 
                using (SqlConnection conn = new SqlConnection("...")) 
                { 
                    conn.Open(); 
                    SqlCommand sqlCommand = new SqlCommand(); 
                    sqlCommand.Connection = conn; 
                    sqlCommand.CommandText = ...
                    sqlCommand.ExecuteNonQuery(); 
 
                    ...
                }  
                scope.Complete(); 
            }
Starting with EF6.0 we have a new way to work with transactions. The new approach is based on Database.BeginTransaction(), Database.Rollback(), Database.Commit(). Yes, no more TransactionScope.
In the following example we create a new transaction and use it to commit our changes.
using (var dbContextTransaction = context.Database.BeginTransaction()) 
                { 
                    try 
                    { 
                        context.Foo.Add(foo);
                        context.Foo.Add(foo);
                        context.SaveChanges();
                        dbContextTransaction.Commit(); 
                    } 
                    catch (Exception) 
                    { 
                        dbContextTransaction.Rollback(); 
                    } 
                } 
The new feature allow us to pass an existing transaction between different context and even reuse an existing transaction using Database.UseTransaction().
using (var context = new FooContext(conn, contextOwnsConnection: false)) 
                        { 
                           context.Database.UseTransaction(myTransaction); 
         context.Foo.Add(foo);
                           context.SaveChanges(); 
                        } 
 
                        myTransaction.Commit(); 
The next tables shows what are the features supported by the new way to execute transaction in comparison with the old one.
Property
Database.BeginTransaction
(new feature)
TransactionScope
(old feature)
Is recommended by Microsoft in EF6
Yes
No
Only database related operation in transaction
Yes
No
Mixing DB operation and C# code in the transaction
No
Yes
Wrap Database.ExecuteSqlCommand in transaction if no transaction is specified
Yes
No
Can specify to a new DataContext an existing transaction that will be used
Yes
No
Can be database connection managed manually and specified to objects
Yes (to DataContext)
Partially Yes
Can the isolation level to be controlled
Yes
Yes
Can execute pure SQL queries in a custom transaction
Yes
Yes
Async calls supported in a transaction
Yes
Yes (from .NET 4.5.1, with some small limitation)
Where the transaction is managed and controlled
By developer from code
In background, by the .NET Core
Full control of the transaction
Yes
Partially
Existing transaction can be used
Yes
No

There is limitation related to retry policy. When we are doing multiple SaveChanges to our contexts, the retry policy would not know what data was saved or not. See below example:
using (var db = new FooContext()) 
{ 
    using (var transaction = db.Database.BeginTransaction()) 
    { 
        db.Foos.Add(new Foo()); 
        db.SaveChanges(); 
 
        db.Foos.Add(new Foo()); 
        db.SaveChanges(); 
 
        transaction.Commit(); 
    } 
}
In this case we have a workaround by suspending the retry execution strategy for that peace of code or specify a manual call execution strategy. I highly recommend to review this situations and try to create a store procedure that execute all the necessary changes in one transaction. It is safer and can be controlled easier.
The new way of using transactions is the one that is recommended by EF Team. This is the way how we should use transactions.
Read More »

Using Transaction Scope in Entity Framework

Entity Framework transaction scope examples

Transactions as a core building block of entity framework. The DbContext object which we heavily use for interacting with the database uses transactions internally without you having to do anything extra. In case you need to manually provide transaction support, here is how you can do it.
In this post, I cover three cases in which transaction scope is being used to show rollback when an error occurs during an update of multiple entities:
  1. when you have multiple save calls to the context;
  2. when you have  single save with multiple object; and
  3. transactions across multiple contexts.
Let’s first review our simple model and context object. To re-create this project, use Visual Studio 2010 console project with Entity Framework 5.0 RC bits from nuget.
Our model is a simplified version of a “Product” object and it’s definition look like this:
using System;
using System.ComponentModel.DataAnnotations;
 
namespace EFTransactionsDemo.Model
{
    public class Product
    {
        public int Id { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public DateTime DateAdded { get; set; }
    }
}



Our context definition looks like:

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
 
namespace EFTransactionsDemo.Model
{
    public class EFTDbContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
         
        public EFTDbContext() : 
            base("EFTransactionsDemo")
        {
            // disable proxy creation 
            this.Configuration.ProxyCreationEnabled = false;
        }
         
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Tell Code First to ignore PluralizingTableName convention
            // If you keep this convention then the generated tables will have pluralized names.
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }
}



The main method (this is demo code; in production environments, please make sure to use proper programming paradigms, do error handling, parameter checks, etc.), calls three functions that demonstrate the use of transaction scope for the three scenarios I listed above.

using System;
using System.Linq;
using System.Transactions;
using EFTransactionsDemo.Model;

namespace EFTransactionsDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // setup
            CreateDatabase();

            // transaction demo - 1
            // wrap multiple save calls within one transaction
            // and roll back on a bad one
            TestMultipleSaveCalls();

            // transaction demo - 2
            // wrap multiple adds and one save within one transaction
            // and roll back on a bad one
            TestMultipleAddWithOneSaveCall();

            // transaction demo - 3
            // multiple contexts in same transaction
            TestMultipleContexts();
        }

        private static void TestMultipleSaveCalls()
        {
            // good product
            Product goodProduct = new Product()
            {
                Name = "My awesome book",
                DateAdded = DateTime.UtcNow
            };

            // invalid product
            Product invalidProd = new Product()
            {
                Name = "My awesome book part 2"
                // note - no date added specified
            };

            // define our transaction scope
            var scope = new TransactionScope(
                // a new transaction will always be created
                TransactionScopeOption.RequiresNew,
                // we will allow volatile data to be read during transaction
                new TransactionOptions()
                {
                    IsolationLevel = IsolationLevel.ReadUncommitted
                }
            );

            try
            {
                // use the scope we just defined
                using (scope)
                {
                    // create a new db context
                    using (var ctx = new EFTDbContext())
                    {
                        // add the product
                        ctx.Products.Add(goodProduct);
                        // save
                        ctx.SaveChanges();

                        // add the invalid product
                        ctx.Products.Add(invalidProd);
                        // save
                        ctx.SaveChanges();
                    }

                    // everything good; complete
                    scope.Complete();
                }
            }
            catch { }

            // verify that we actually rolled back
            using (var ctx = new EFTDbContext())
            {
                Console.WriteLine(ctx.Products.Count());
            }
        }

        private static void TestMultipleAddWithOneSaveCall()
        {
            // good product
            Product goodProduct = new Product()
            {
                Name = "My awesome book",
                DateAdded = DateTime.UtcNow
            };

            // invalid product
            Product invalidProd = new Product()
            {
                Name = "My awesome book part 2"
                // note - no date added specified
            };

            // define our transaction scope
            var scope = new TransactionScope(
                // a new transaction will always be created
                TransactionScopeOption.RequiresNew,
                // we will allow volatile data to be read during transaction
                new TransactionOptions()
                {
                    IsolationLevel = IsolationLevel.ReadUncommitted
                }
            );

            try
            {
                // use the scope we just defined
                using (scope)
                {
                    // create a new db context
                    using (var ctx = new EFTDbContext())
                    {
                        // add the product
                        ctx.Products.Add(goodProduct);

                        // add the invalid product
                        ctx.Products.Add(invalidProd);
                        // save
                        ctx.SaveChanges();
                    }

                    // everything good; complete
                    scope.Complete();
                }
            }
            catch { }

            // verify that we actually rolled back
            using (var ctx = new EFTDbContext())
            {
                Console.WriteLine(ctx.Products.Count());
            }
        }

        private static void TestMultipleContexts()
        {
            // good product
            Product goodProduct = new Product()
            {
                Name = "My awesome book",
                DateAdded = DateTime.UtcNow
            };

            // invalid product
            Product invalidProd = new Product()
            {
                Name = "My awesome book part 2"
                // note - no date added specified
            };

            // define our transaction scope
            var scope = new TransactionScope(
                // a new transaction will always be created
                TransactionScopeOption.RequiresNew,
                // we will allow volatile data to be read during transaction
                new TransactionOptions()
                {
                    IsolationLevel = IsolationLevel.ReadUncommitted
                }
            );

            try
            {
                // use the scope we just defined
                using (scope)
                {
                    // create a new db context
                    var firstCtx = new EFTDbContext();
                    // create a second context
                    var secondCtx = new EFTDbContext();

                    // add the product to first context
                    firstCtx.Products.Add(goodProduct);
                    // save
                    firstCtx.SaveChanges();

                    // add the invalid product to second context
                    secondCtx.Products.Add(invalidProd);
                    // save
                    secondCtx.SaveChanges();

                    // everything good; complete
                    scope.Complete();
                }
            }
            catch { }

            // verify that we actually rolled back
            using (var ctx = new EFTDbContext())
            {
                Console.WriteLine(ctx.Products.Count());
            }
        }

        private static void CreateDatabase()
        {
            using (var ctx = new EFTDbContext())
            {
                ctx.Database.CreateIfNotExists();
            }
        }
    }
}

As always, please do leave your thoughts and comments. It is the user feedback that drives the blog better.
Read More »

Thursday, January 8, 2015

New Logical Functions in SQL Server 2012 (IIF and CHOOSE)

Problem
SQL Server 2012, has new features to perform Logical Functions such as CHOOSE and IIF that can be used to perform logical operations. In this tip we take a look at some basic examples of how this could be used.
Solution
In this tip we will discuss how to utilize the below mentioned newly introduced Logical Functions in SQL Server 2012.
1. CHOOSE Logical Function
2. IIF Logical Function

Using CHOOSE Logical Function

The CHOOSE function, which is available in SQL Server 2012, will return the item at the specified index from the list of values which are available. In this example we have 3 values and we are asking to select the third value in the list which is "SQL Server 2012".

SELECT 'New SQL Server Release' = CHOOSE(3, 'SQL Server 2008', 'SQL Server 2008 R2',
'SQL Server 2012')
GO


sql server CHOOSE function

Let's execute the below TSQL code which will use the CHOOSE function to return the month value using the CHOOSE logical function.

Use AdventureWorks2008R2
GO
SELECT 
  DISTINCT(FirstName + ' ' + LastName) AS Name
 ,DATEPART(DD, ModifiedDate) AS [Date]
 ,CHOOSE(DATEPART(MM,ModifiedDate),'January','February','March','April','May','June',
  'July','August','September','October','November','December')[Month]
 ,DATEPART(YYYY, ModifiedDate) AS [Year]
FROM [Person].[Person] 
 ORDER BY Name ASC
GO


sql server result set using the choose function in denali

Using IIF Logical Function

The IIF function, which is available in SQL Server 2012, returns one of the two values depending upon whether the Boolean expression evaluates to either True or False.

DECLARE @FirstArgument INT = 10
DECLARE @SecondArgument INT = 20
SELECT IIF ( @FirstArgument > @SecondArgument , 'TRUE', 'FALSE' ) 
 AS [Output Using IIF Logical Function]
GO

sql server 2012 iif logical function

Let's execute the below TSQL code which will use the IIF function to return the results. The logic is if the StateProvinceCode is between 0 and 95 then return France otherwise return Canada.
Use AdventureWorks2008R2
GO
SELECT 
   StateProvinceCode
  ,CountryRegionCode
  ,IIF(TRY_PARSE(StateProvinceCode AS INT) Between 0 AND 95,'France','Canada') AS Country
FROM Person.StateProvince 
  WHERE StateProvinceCode IN ('95','AB')
GO


sql server 2012 iif logical function
Read More »

Tuesday, December 30, 2014

Recent Posts

My Blog List