CODEWITHSUNDEEP
sqlsql-server
kalls
kalls

Reputation: 2855

Copy rows from the same table and update the ID column

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?

Upvotes: 38

Views: 148547

Answers (10)

DataMaat
DataMaat

Reputation: 1

If you don't want to type all column names, you can run this procedure. Procedure get all columnames except the identity and timestamp columns. Returns 0 if nothing inserted, returns new max ID if inserted succeeds. Of course assume the identity is an int datatype.


-- =============================================
-- Author:      DataMaat
-- Create date: 
-- Description: copy record to another record in same table
-- =============================================
CREATE PROCEDURE proc_copyrecord
    @table nvarchar(50)
    ,@where nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @sql nvarchar(max)
    declare @colnames nvarchar(max)
    declare @ident nvarchar(50)
    declare @inserted int

    set @ident=(Select name FROM sys.columns WHERE object_id = OBJECT_ID(@table) and is_identity=1)

    --get columnnames
    --no identity, timestampcols
    Select @colnames=  
        case when @colnames Is Null then [name]
            else @colnames + ',' + [name]
        end
    FROM sys.columns c WHERE object_id = OBJECT_ID(@table)  and is_identity=0 and system_type_id189

    set @sql='Insert into ' + @table + ' (' + @colnames + ')
                Select ' + @colnames + ' From ' + @table + ' Where ' + @where
    --print @sql
    exec(@sql)
    
    set @inserted=@@ROWCOUNT
    set @sql='select case when ' + CAST(@inserted as varchar) + '=0 then 0 else (select max(' + @ident + ') from ' + @table + ') end as NewID'
    --print @sql
    exec(@sql)

END
GO

Upvotes: 0

Blrfl
Blrfl

Reputation: 7003

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.

Upvotes: 94

Jimmy T.
Jimmy T.

Reputation: 111

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

}

Upvotes: 1

Jess Thiri Lwin
Jess Thiri Lwin

Reputation: 21

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

Upvotes: -1

Franta
Franta

Reputation: 1024

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.

Upvotes: 1

Prabin Poudel
Prabin Poudel

Reputation: 73

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`

Upvotes: 1

Divya
Divya

Reputation: 369

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.

Upvotes: 1

Ahmed Elzeiny
Ahmed Elzeiny

Reputation: 67

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)

Upvotes: 5

Donald Powell
Donald Powell

Reputation: 774

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'

Upvotes: 24

onedaywhen
onedaywhen

Reputation: 57023

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;

Upvotes: 4

Related Questions