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?
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
}
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
insert into Table (DefID,ProdID,Definition,Desc)
select DefID,15,Definition,Desc from Table where vo_user='jloe';
