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';