Bulk Update in SQL Server:

Bulk Update in SQL Server: 


Example:

CREATE PROCEDURE BulkUpdate

    @FilePath NVARCHAR(500)  -- Path to the single CSV file containing both Orders and OrderDetails data

AS

BEGIN

    BEGIN TRY

        BEGIN TRANSACTION;


        -- Create temporary staging tables for Orders and OrderDetails

        CREATE TABLE #TempOrders (

            OrderID INT,

            CustomerID INT,

            OrderDate DATETIME,

            TotalAmount DECIMAL(18, 2)

        );


        CREATE TABLE #TempOrderDetails (

            OrderDetailID INT,

            OrderID INT,

            ProductID INT,

            Quantity INT,

            UnitPrice DECIMAL(18, 2)

        );


        -- Bulk insert data from the single CSV file into the #TempOrders table

        BULK INSERT #TempOrders

        FROM @FilePath

        WITH (

            FIELDTERMINATOR = ',',  -- Assuming comma-separated values

            ROWTERMINATOR = '\n',    -- Assuming newline-separated rows

            FIRSTROW = 2            -- Skips the header row

        );


        -- Bulk insert data into #TempOrderDetails (you need to filter the file or adapt this section)

        -- Assuming the OrderDetails data starts from a certain row or can be identified in the file.

        BULK INSERT #TempOrderDetails

        FROM @FilePath

        WITH (

            FIELDTERMINATOR = ',',  

            ROWTERMINATOR = '\n',    

            FIRSTROW = 2            

        );


        -- Step 1: Merge Orders data

        MERGE INTO Orders AS target

        USING #TempOrders AS source

        ON target.OrderID = source.OrderID

        WHEN MATCHED THEN

            UPDATE SET 

                target.CustomerID = source.CustomerID,

                target.OrderDate = source.OrderDate,

                target.TotalAmount = source.TotalAmount

        WHEN NOT MATCHED BY TARGET THEN

            INSERT (OrderID, CustomerID, OrderDate, TotalAmount)

            VALUES (source.OrderID, source.CustomerID, source.OrderDate, source.TotalAmount);


        -- Step 2: Merge OrderDetails data

        MERGE INTO OrderDetails AS target

        USING #TempOrderDetails AS source

        ON target.OrderDetailID = source.OrderDetailID

        WHEN MATCHED THEN

            UPDATE SET 

                target.Quantity = source.Quantity,

                target.UnitPrice = source.UnitPrice

        WHEN NOT MATCHED BY TARGET THEN

            INSERT (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice)

            VALUES (source.OrderDetailID, source.OrderID, source.ProductID, source.Quantity, source.UnitPrice);


        -- Step 3: Commit the transaction if everything is successful

        COMMIT TRANSACTION;


    END TRY

    BEGIN CATCH

        -- In case of error, roll back the transaction and raise the error

        ROLLBACK TRANSACTION;

        THROW;

    END CATCH;

END;





Execution:

EXEC BulkUpdate @FilePath = 'D:\data\orders_details.csv';

Comments

Post a Comment

Popular posts from this blog

Multiline to singleline IN C# - CODING

Multiline to singleline IN C# - CODING using System; using System.Linq; class Program {     static void Main(string[] args)     {         string input = @"This is                  multiline";                  // Split the input into lines, trim each line, and filter out empty lines         string result = string.Join(" ", input.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)                                                .Select(line => line.Trim()));         Console.WriteLine(result);     } } Output: This is  multiline
Read more

EF Core interview questions for beginners

1. What is Entity Framework Core (EF Core)? EF Core is an open-source Object-Relational Mapper (ORM) that enables .NET developers to work with databases using .NET objects. It allows you to interact with a database using strongly typed classes and LINQ queries, rather than writing raw SQL queries. EF Core supports various database providers, such as SQL Server, SQLite, PostgreSQL, MySQL, and more. 2. What are the advantages of using EF Core over traditional ADO.NET? Productivity:  EF Core allows you to work with databases using high-level objects, reducing the amount of boilerplate code you need to write. Cross-platform:  EF Core supports multiple databases and works on Windows, Linux, and macOS. LINQ Support:  You can query the database using LINQ, which is more type-safe and easier to maintain than raw SQL queries. Code-First & Database-First:  EF Core supports both approaches to model databases. Change Tra...
Read more

EF Core interview questions for experienced

EF Core interview questions for experienced 1. What are the main differences between Entity Framework (EF) and Entity Framework Core (EF Core)? EF Core is a complete rewrite of EF. Unlike EF, EF Core supports cross-platform development, improved performance, LINQ query translation, and has a more modular architecture. EF Core does not support all features that EF 6 does (like lazy loading and some database providers). 2. How does EF Core handle migrations, and what are some common pitfalls? EF Core uses migrations to update the database schema based on model changes. Common pitfalls include not applying migrations correctly, misalignment between model and database state, or missing/overwriting migrations during collaboration in a team environment. 3. Explain the difference between Add and Attach methods in EF Core. Add method is used to add a new entity to the context and mark it as Added , while Attach attaches an existing entity to the context with no changes. The Attach meth...
Read more