Copying row from 1 table to another in SQL

Question

When a user deletes a row in the database, I want to archive it into a separate table in the database rather than delete it or flag it in the current table. I figure I would need to do something like in this link:

How to copy a row from one SQL Server table to another

The thing is, the archive table has 1 extra column in it that does not match the original table (ArchiveTimeStamp). This ArchiveTimeStamp does not exist in the original table, instead I would use something like

archiveComm.Parameters.AddWithValue("ArchiveTimeStamp", Date.Time.Now);

This is what I have so far:

 SqlCommand archiveComm = new SqlCommand("INSERT INTO Archive_Table SELECT * FROM Table WHERE RowID = @rowID", conn);

Is there a way for me to modify the SqlCommand to add another param that doesn't exist in the original Table?

Answer 1

Why not just handle this on the back end? You can create a trigger on the original table to insert into another table after every delete? Your trigger will look like this:

CREATE TRIGGER onOriginalTableDelete
ON originalTable
FOR DELETE
AS 
INSERT INTO anotherTable
SELECT * FROM deleted;

When a record is deleted on the original table, it will insert the deleted record into the other table. You might want to read on using the deleted table here.

Check this SQL Fiddle. Since you're inserting the timestamp in another column, you can just add this on the INSERT INTO SELECT statement:

INSERT INTO OtherTable
SELECT *, CURRENT_TIMESTAMP FROM MainTable;

This could be the query for your trigger:

CREATE TRIGGER onOriginalTableDelete
ON originalTable
FOR DELETE
AS 

INSERT INTO anotherTable
SELECT *, CURRENT_TIMESTAMP FROM deleted;

Answer 2

Good question. I'd suggest (as Gian has also suggested) moving the logic you require to backup the deleted row into a trigger that gets fired on delete.

Triggers are events in a database associated to a table which get fired upon an action occurring i.e. insert / update / delete.

So in your scenario, if you create an ON DELETE trigger in the source table, it will get fired when a delete occurs. The SQL contained within the trigger can specify what to do with the deleted data, which in your scenario will be: insert the deleted info into the archive table with a timestamp.

So if you have:

Source_Table:
Col_1
Col_2
Col_3

Archive_Table:
Col_1
Col_2
Col_3
Time_Stamp

You'll need to create a FOR DELETE trigger against Source_Table (something like this):

CREATE TRIGGER SourceDeletedTrigger
ON database.dbo.Source_Table
FOR DELETE
AS

INSERT INTO Archive_Table(Col_1, Col_2, Col_3, Time_Stamp)
  SELECT
    DELETED.Col_1,
    DELETED.Col_2,
    DELETED.Col_3,
    GETDATE()
  FROM DELETED

GO

The above is some rough SQL which may contain a couple of syntax errors but the guts of the idea is conveyed.

Answer 3

You will have to use to explicit column list and values form of the INSERT statement:

INSERT INTO Archive_Table (
     Col1
    ,Col2
    ,Col3 )
SELECT
     Col1
    ,Col2
    ,Col3
FROM
   Table
WHERE
   Row_ID = @Row_ID

See Insert into ... values ( SELECT ... FROM ... )

Answer 4

I think you have to specify the columns with something like this

INSERT INTO tab1
(col1, col2)
SELECT col1, col2
FROM tab2
WHERE  RowID = @rowID"

Answer 5

You need to specify the columns name in that case:

 archiveComm.Parameters.AddWithValue("ArchiveTimeStamp", Date.Time.Now);
 string SQL = "INSERT INTO Archive_Table (Col1,Col2,ArchiveTimeStamp) " & _
              "SELECT Col1,Col2,@ArchiveTimeStamp FROM Table WHERE RowID = @rowID"
 SqlCommand archiveComm = new SqlCommand(SQL, conn);

Answer 6

Here is my suggestion, you are forced to supply the column names or it won't let you run the query, however I understand you would prefer a generic solution that worked for any table so I suggest building the insert SQL dynamically, cache it on your application, and then just execute it with your extra archive column. Here is a c# example:

public class ArchiveTableRow
{
    private static readonly IDictionary<string, string> _cachedInsertStatements = new Dictionary<string, string>();

    public void Archive(string tableName, string rowId)
    {   
        if (_cachedInsertStatements.ContainsKey(tableName) == false)
        {
            BuildInsertStatement(tableName);
        }

        var insertQuery = _cachedInsertStatements[tableName];

        // ...      
        SqlCommand archiveComm = new SqlCommand(insertQuery, conn);
        // ...      
        archiveComm.Parameters.AddWithValue("ArchiveTimeStamp", Date.Time.Now);     
        // ...      
    }

    private void BuildInsertStatement(string tableName)
    {
        // Get the columns names:
        var getColumnNamesQuery = @"
            SELECT Table_Schema, Column_Name
            FROM Information_Schema.Columns
            WHERE Table_Name = '" + tableName + "'
            Order By Ordinal_Position";

        // Execute the query
        SqlCommand archiveComm = new SqlCommand(getColumnNamesQuery, conn);

        // Loop and build query and add your archive in the end

        // Add to dictionary    
    }   
}

You would use it with something like:

var archiveRow = new ArchiveTableRow();

archiveRow.Archive("TableName", rowId);