How to copy one table from another and delete the Table

Question

User766369706 posted

Hi, I am doing a shopping application where I need to transfer the entire ShoppingCart Table to the OrderTable. I try researching various website but I am still not getting the desirable answer and codes. May I know is there any method to copy from the ShoppingCart Table to the OrderTable using a button and copy the table contents?

CREATE TABLE [dbo].[ShoppingCart] (
    [CartID]      INT            IDENTITY (1, 1) NOT NULL,
    [ProductID]   INT            NULL,
    [ProductName] NVARCHAR (MAX) NULL,
    [Size]        NCHAR (10)     NULL,
    [Price]       DECIMAL (18)   NULL,
    PRIMARY KEY CLUSTERED ([CartID] ASC)
);

CREATE TABLE [dbo].[Order] (
    [OrderID]       INT            IDENTITY (1, 1) NOT NULL,
    [ProductID]     INT            NULL,
    [ProductName]   NVARCHAR (MAX) NULL,
    [Size]          NCHAR (10)     NULL,
    [Price]         DECIMAL (18)   NULL,
    [TransactionID] INT            NULL,
    PRIMARY KEY CLUSTERED ([OrderID] ASC)
);

 

Answer 1

User-183374066 posted

Hi Alvin,

I think you need one more additional column for Customer. I just prepare a script for you. 

CREATE TABLE [dbo].[ShoppingCart] (
    [CartID]      INT            IDENTITY (1, 1) NOT NULL,
    [ProductID]   INT            NULL,
    [ProductName] NVARCHAR (MAX) NULL,
    [Size]        NCHAR (10)     NULL,
    [Price]       DECIMAL (18)   NULL,
	[CustomerId]  INT			NOT NULL,
    PRIMARY KEY CLUSTERED ([CartID] ASC)
)
GO

CREATE TABLE [dbo].[Order] (
    [OrderID]       INT            IDENTITY (1, 1) NOT NULL,
    [ProductID]     INT            NULL,
    [ProductName]   NVARCHAR (MAX) NULL,
    [Size]          NCHAR (10)     NULL,
    [Price]         DECIMAL (18)   NULL,
	[CustomerId]  INT			NOT NULL,
    [TransactionID] INT            NULL,
    PRIMARY KEY CLUSTERED ([OrderID] ASC)
)
GO

DECLARE @CustomerId INT = 1;
DECLARE @TransactionId INT = 1;

INSERT INTO dbo.[Order] (ProductId, ProductName, Size, Price, CustomerId, TransactionID)
SELECT ProductID, ProductName, Size, Price, CustomerId, @TransactionId FROM dbo.ShoppingCart
WHERE CustomerId = @CustomerId

Answer 2

User766369706 posted

Hi! Thanks for helping with my project. Thanks to your code, it works. However, for the transactionID, I want it to be inputted by myself where it as I need to make transactionID as a foreign key to display another data. Below is an example of what I mean

    public int copyTable(int transactionId)
    {
        int result = 0;
        SqlConnection myConnection = new SqlConnection(strConnectionString);
        myConnection.Open();

        String strCommandText = "INSERT INTO dbo.[Order] (ProductId, ProductName, Size, Price, TransactionID) SELECT ProductID, ProductName, Size, Price, @TransactionId FROM dbo.ShoppingCart";

        SqlCommand SQLCommand = new SqlCommand(strCommandText, myConnection);
        //TransactionID is a key that is unique
        SQLCommand.Parameters.AddWithValue("@TransactionId", transactionId);


        result += SQLCommand.ExecuteNonQuery();
        myConnection.Close();

        return result;
    }