copy data from one table to another using stored procedure

Question

I want to copy data to A_Table data from B_Table

then finally delete the B_Table data

I just created following

CREATE PROCEDURE [dbo].[Copy_ProductStatistics]

  (@Product_ID nvarchar, @ProductName nvarchar,@CreatedDate datetime)

AS

BEGIN
        INSERT INTO A_Table(Product_ID, ProductName,CreatedDate)

        SELECT Product_ID, ProductName,CreatedDate
        FROM B_Table
        WHERE (Product_ID = @Product_ID AND ProductName = @ProductName AND CreatedDate = @CreatedDate

        DELETE FROM  B_Table
END

then I called it like following

    [HttpGet]
    public ActionResult Copy_DatatoProductStatistics()
    {
        var incomplete_product_result = db.Database.SqlQuery<ProductStatistics>("Copy_ProductStatistics");

        return RedirectToAction("FileUpload", "FileUpload");
    }

this is not popping any error seems like ,problem is in stored procedure

You don't seem to be passing an parameters to the stored procedure, so `@Product_ID` is null and it doesn't copy anything. If I create a stored procedure, I always test it first before hooking it up to EF, so if something stops working I know it's not the proc itself, more likely how I'm calling it. — Rhumborl, Jan 26 '16 at 09:19
I'm guessing that you're using Entity Framework? How are you passing the params to the stored procedure? :) http://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first — auo, Jan 26 '16 at 09:19
Your sp is not returning any data. What do you expect to be returned in incomplete_product_result? — mnieto, Jan 26 '16 at 09:20
@mnieto Actually I dont want return any data, just want to copy data from one table to another and delete data from initial table — kez, Jan 26 '16 at 09:21
In that case, you can use `db.Database.ExecuteSqlCommand` instead — mnieto, Jan 26 '16 at 09:25

score 0 · Answer 1 · answered Jan 26 '16 at 10:15

Here are an example calling an stored procedure with parameters. The sp don't returns any data: (ctx.ExecuteCommand is a wrapper to the Database.ExecuteSqlCommand method)

    const string sql = "DeleteVersion @pVersiontId";
    using (IContext ctx = DI.Container.Resolve<IContext>()) {
        try {
            ctx.ExecuteCommand(sql, new SqlParameter("@pVersiontId", versionId));
        } catch (SqlException ex) {
            LoggerFactory.CreateLog().LogError(String.Format(Resources.CouldNotDeleteDeVersion, versionId), ex);
            throw new CannotDeleteVersionExcpetion(String.Format(Resources.CouldNotDeleteDeVersion, versionId), ex);
        }
    }

copy data from one table to another using stored procedure

1 Answers1