8 Replies Latest reply on Mar 8, 2007 8:34 AM by insuractive

    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?
        • 1. Re: Copy SQL Data to Another Table
          insuractive Level 3
          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'

          • 2. Re: Copy SQL Data to Another Table
            Dan Bracuk Level 5
            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
            • 3. Re: Copy SQL Data to Another Table
              Redcarl Level 1
              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.
              • 4. Copy SQL Data to Another Table
                insuractive Level 3
                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)
                • 5. Re: Copy SQL Data to Another Table
                  Redcarl Level 1
                  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;
                  • 6. Re: Copy SQL Data to Another Table
                    insuractive Level 3
                    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.
                    • 7. Re: Copy SQL Data to Another Table
                      Redcarl Level 1
                      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?
                      • 8. Re: Copy SQL Data to Another Table
                        insuractive Level 3
                        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.