with - update table from another database sql server




Copy rows from the same table and update the ID column (4)

I have the following table

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.

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?


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.


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)




sql-server