Copy rows from the same table and update the ID column

I have the following table

I have inserted Product B to it and it gives me a开发者_运维百科n ID of 15

Then I have the definition table which is as follows.

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

How to achieve this using SQL code?

---

INSERT INTO ProductDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

---

This will work with any column you choose. Not just primary key/ID.

INSERT INTO TableName (Column1, CustomID, Column3, Column4, Column5)
SELECT Column1, 'NewValue', Column3, Column4, Column5 FROM TableName
WHERE CustomID='OrigValue'

---

if you want to select all items (in condition the table not contains any primary keys)

INSERT INTO [tabelName]
SELECT  * FROM    [tabelName]
WHERE  (YourCondition)

in condition the table contains a primary keys, select only the columns that not primary key Like:

INSERT INTO [tabelName]
SELECT  col_1,col_2,col_n FROM    [tabelName]
WHERE  (YourCondition)

---

Can use MERGE on SQL Server 2008, has the advantage of using OUTPUT to return the DefID values, assuming they are auto-generated e.g.

MERGE INTO ProductDefinition
USING (
       SELECT 16, P1.Definition, P1.Description
         FROM ProductDefinition AS P1
        WHERE P1.ProdID = 15
      ) AS source (ProdID, Definition, Description)
ON 0 = 1
WHEN NOT MATCHED THEN
   INSERT (ProdID, Definition, Description)
   VALUES (ProdID, Definition, Description)
   OUTPUT inserted.DefID, inserted.ProdID, 
             inserted.Definition, inserted.Description;

---

If you want to replicate data in same table use this logic:

first, insert statment where you want to insert...

insert into [table](column1,column2)

second, select statment from where you want to take data for insertion....

select (column1/'your value',column2/'your value') from [table]

now set filter which rows you want to duplicate

where (your condition)

as want to replicate same data for different customers i have used this query.

---

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`

---

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.

---

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

}

---

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