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?
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)