with - update table from another database sql server
Copy rows from the same table and update the ID column (4)
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;
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?
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)