Copy SQL Data to Another Table

[Redcarl]

I have a simple Stored Procedure in SQL that deletes a row from a table based on a variable that comes fom a CF app. I would like to be able to copy this row to another table to use as a Delete Log. The destination table has different field names since more than one table will use this as a delete log. I would like to have it copy the row depending on the data coming from the CF form, and then delete it, and I want to accomplish it through the Stored Procedure. Anyone have any TSQL code that will copy this row into a table with different field names?

[insuractive]

You should be able to use a combination of Insert Select and column aliases to accomplish the task. You just have to make sure you alias all the columns that will be in your log table

i.e. Say your processed table has the fields id and name and your destination table has the fields topic_id, detail, remote and actiondate:

INSERT Into myLogTable
SELECT id as 'topic_id', name as 'detail', 'remote' = 0, 'actiondate' = getdate()
FROM myEditedTable
WHERE conditions = 'true'

[Dan_Bracuk]

I don't know if insuractive's code will work or not. But I do know that this will work

insert into yourtable
(fields)
select values from etc

[Redcarl]

This works as long as the field names are the same in each table. I tried it with my situation and it errored out because the fields in the destination table are different.

[insuractive]

That is why I suggested you use aliases to match your select statement fields to your destination table fields. Its a quick way to "map" your select table to your insert table and lets you get away with doing Insert/Select statements even if the 2 tables differ in structure. And it works great in MS SQL (which I assume you are using since you mentioned T-SQL)

[Redcarl]

I tried using the aliases and still I get an error about the table definition: Insert Error: Column name or number of supplied values does not match table definition. Not sure what I am doing wrong with it. Here is the code:

(@Manufacturer_ID [int],
@Manufacturer [nvarchar] (50))

AS

INSERT Into Delete_Log
SELECT Manufacturer_ID as 'Item_ID', Manufacturer as 'Item'
FROM Manufacturers
WHERE Manufacturer_ID = @Manufacturer_ID;


DELETE FROM Manufacturers

WHERE Manufacturer_ID = @Manufacturer_ID;

[insuractive]

what is the structure of your delete_log table? You have to make sure that your SELECT statement has the same type/number of fields in the same order as your Delete_log table.

[Redcarl]

The Delete_Log table has the two fields in the Select statement, as well as an incrementing field (Delete_ID) and a field that auto inserts the date (Delete_Date). These two wouldn't need to be copied over so I didn't put them in the Select statement. Can it be done without copying them over?

[insuractive]

The Insert/Select SQL statement requires that the structure of your select statement match the structure of the table you are trying to insert into. You may be able to leave off the incrementin field, but you probably have to include the delete_date field in your select statement:

INSERT Into Delete_Log
SELECT Manufacturer_ID as 'Item_ID', Manufacturer as 'Item', getdate() as 'Delete_Date'
FROM Manufacturers
WHERE Manufacturer_ID = @Manufacturer_ID;

Also, make sure you list the fields in the exact order that they appear in your Delete_Log table.