Copy rows from the same table and update the ID column

user_529866 0
Viewed 119671

I have the following table

alt text

I have inserted Product B to it and it gives me an ID of 15

Then I have the definition table which is as follows.

alt text

I want to select the ProductDefinition rows where ProdID = 14 and replicate the same and insert it for ProdID = 15 like the following

alt text

How to achieve this using SQL code?

sql-server sql
9 Answers

If your id is not autoincrement or declared sequence and if u dont want to create a temporary table:

you can use:

INSERT INTO Tabel1 SELECT ((ROW_NUMBER( ) OVER ( ORDER BY ID  )) + (SELECT  MAX(id) FROM Table1)) ,column2,coulmn3,'NewValue' FROM Tabel1 Where somecolumn='your value`
user_52952120

Do you use Oracle? It does not have an automatic PK_generator, nothing to work for your INSERT silently. However, it has SEQUENCEs, so let's use its NEXTVAL:

INSERT INTO Orders_tab (Orderno, Custno)
    VALUES (Order_seq.NEXTVAL, 1032);

The INSERT operation is exactly the case for them, the purpose of a SEQUENCE, you just have to use it explicitly. More described: Managing Sequences # Using Sequences

The node for Sequences is on the level of Tables, i.e. in the SQLdeveloper. Ours are ID_GENERATOR, in every DB.

user_81207750 
insert into Table (DefID,ProdID,Definition,Desc)
       select DefID,15,Definition,Desc from Table where vo_user='jloe';
user_39676340

Here is a generic version.

Usage: EXEC TableRowCopy '[table_name]', [row_id]

sample: EXEC TableRowCopy 'Customers', 32767

Make sure you change the

{

@IdColumnName VARCHAR(50) = 'Id'

}

to reflect the actual column name of your ID column. This SP also assume auto-generated ID's

{

/****** Object:  StoredProcedure [dbo].[TableRowCopy]   ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TableRowCopy](
    @TableName VARCHAR(50),
    @WhereIdValue INT,
    @IdColumnName VARCHAR(50) = 'Id'
)
AS
BEGIN
    DECLARE @columns VARCHAR(5000), @query VARCHAR(8000);
    SET @query = '' ;

    SELECT @columns =
        CASE
            WHEN @columns IS NULL THEN column_name
            ELSE @columns + ',' + column_name
        END
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (
        TABLE_NAME = LTRIM(RTRIM(@TableName))
        AND
        column_name != LTRIM(RTRIM(@IdColumnName))
    );

    SET @query = 'INSERT INTO ' + @TableName + ' (' + @columns + ') SELECT ' + @columns + ' FROM ' + @TableName + ' WHERE ' + @IdColumnName + ' = ' + CAST(@WhereIdValue AS VARCHAR);
    EXEC (@query);
    SELECT SCOPE_IDENTITY();
END

}

user_25567400
Related