具有一个不同列值的重复行 [英] Duplicate Rows with one diffrent Column value

问题描述

 TranDate Amount EvolutionCreditAccount EvolutionDebitAccount已完成
 
 31/01/2013 12:00:00 AM 262033.83 1020 / MICT / MOS / PISA / 0006 1020 / MICT / MOS / PISA / 0002 1 
 31/01/2013 12:00:00 AM 197237.28 1020 / MICT / MOS / PISA / 0099 1020 / MICT / MOS / PISA / 0033 1 
 31/01/2013 12 ：00：00 AM 980834.24 1020 / MICT / MOS / PISA / 0002 1020 / MICT / MOS / PISA / 0003 1 
 31/01/2013 12:00:00 AM 31579.87 1020 / MICT / MOS / PISA / 0001 1020 / MICT / MOS / PISA / 0002 0 

i希望在sql server中复制值，其中列完成等于''1' '它只会改变它的TranDate值。



EG：我的插值将会跟随

 TranDate Amount EvolutionCreditAccount EvolutionDebitAccount已完成
 
 31/02/2013 12:00:00 AM 262033.83 1020 / MICT / MOS / PISA / 0006 1020 / MICT / MOS / PISA / 0002 1 
 31/02/2013 12:00:00 AM 197237.28 1020 / MICT / MOS / PISA / 0099 1020 / MICT / MOS / PISA / 0033 1 
 31/02/2013 12:00:00 AM 980834.24 1020 / MICT / MOS / PISA / 0002 1020 / MICT / MOS / PISA / 0003 1 

简单地说：

如果我将日期更改为值，它必须复制列完成值为1的所有行，并且仅为其更改TranDate。 div class =h2_lin>解决方案

尝试使用UNION ALL，

  INSERT   INTO 学生（TranDate，Amount，EvolutionCreditAccount，EvolutionDebitAccount，Completed< pre lang =   sql>已完成）
  SELECT  GetDate（），金额，EvolutionCreditAccount，EvolutionDebitAccount，已完成 WHERE 已完成= 1; 
  UNION   ALL  
  SELECT  GetDate（），Amount，EvolutionCreditAccount，EvolutionDebitAccount，已完成 WHERE 已完成= 1; 

您需要在您想要的表名和字段后指定字段名称，我已用 GETDATE（）替换它，它将插入SQL Server的CurrentDatetime，请参阅下面的查询。 />

  INSERT   INTO  ECS_PayRollTableView 
（TranDate，Amount，EvolutionCreditAccount，EvolutionDebitAccount，已完成）
  SELECT  
 GETDATE（）
，金额
，EvolutionCreditAccount 
，EvolutionDebitAccount 
，已完成
  FROM  
 ECS_PayRollTableView 
 其中 Completed = 1 

尝试以下SQL查询 -

 DECLARE @TranDate DATETIME ='' 02/23/2013 12:00:00 AM''， - 重复记录的新日期时间
 @Amount MONEY，
 @EvolutionCreditAccount VARCHAR（MAX），
 @EvolutionDebitAccount VARCHAR（ MAX），
 @Id INT = 1，
 @MaxId INT 
 
 DECLARE @TEMPTABLE TABLE（Id INT IDENTITY（1,1），TranDate DATETIME，Amount MONEY，EvolutionCreditAccount NVARCHAR （MAX），EvolutionDebitAccount NVARCHAR（MAX），已完成BIT）
 
 
 INSERT @TEMPTABLE 
 SELECT * FROM temp 
 WHERE已完成= 1 
 
 SELECT @MaxId = MAX（Id）FROM @TEMPTABLE 
 
 WHILE @Id< = @MaxId 
 BEGIN 
 SELECT 
 @Amount = Amount，
 @EvolutionCreditAccount = EvolutionCreditAccount，
 @EvolutionDebitAccount = EvolutionDebitAccount 
 FROM @TEMPTABLE 
 WHERE Id = @Id 
 
 
 INSERT INTO Tem p 
（
 TranDate，
金额，
 EvolutionCreditAccount，
 EvolutionDebitAccount，
已完成
）
 VALUES 
（ 
 @TranDate，
 @Amount，
 @EvolutionCreditAccount，
 @EvolutionDebitAccount，
 1 
）
 SET @Id = @Id + 1 
 
结束

TranDate        Amount    EvolutionCreditAccount   EvolutionDebitAccount    Completed

31/01/2013 12:00:00 AM  262033.83   1020/MICT/MOS/PISA/0006 1020/MICT/MOS/PISA/0002 1
31/01/2013 12:00:00 AM  197237.28   1020/MICT/MOS/PISA/0099 1020/MICT/MOS/PISA/0033 1
31/01/2013 12:00:00 AM  980834.24   1020/MICT/MOS/PISA/0002 1020/MICT/MOS/PISA/0003 1
31/01/2013 12:00:00 AM  31579.87    1020/MICT/MOS/PISA/0001 1020/MICT/MOS/PISA/0002 0

i want to duplicate values in sql server where column complete is equal to ''1'' and it only changes TranDate value for it.

EG: my inserted values will be following

TranDate        Amount    EvolutionCreditAccount   EvolutionDebitAccount    Completed

31/02/2013 12:00:00 AM  262033.83   1020/MICT/MOS/PISA/0006 1020/MICT/MOS/PISA/0002 1
31/02/2013 12:00:00 AM  197237.28   1020/MICT/MOS/PISA/0099 1020/MICT/MOS/PISA/0033 1
31/02/2013 12:00:00 AM  980834.24   1020/MICT/MOS/PISA/0002 1020/MICT/MOS/PISA/0003 1

simply:
if i change date as value it must duplicate all the rows with column Complete value of 1 and only change TranDate for it.

解决方案

Try using UNION ALL,

INSERT INTO Student(TranDate, Amount,EvolutionCreditAccount, EvolutionDebitAccount,Completed <pre lang="sql">Completed)
SELECT GetDate(), Amount, EvolutionCreditAccount, EvolutionDebitAccount,Completed WHERE Completed=1;
UNION ALL
SELECT GetDate(), Amount, EvolutionCreditAccount, EvolutionDebitAccount,Completed WHERE Completed=1;

You need to specify the field name after the table name and Field which you want as your choice, I have replaced that with GETDATE() which will insert CurrentDatetime of SQL Server see the query below.

INSERT INTO ECS_PayRollTableView
(TranDate,Amount,EvolutionCreditAccount,EvolutionDebitAccount,Completed)
SELECT
    GETDATE()
    ,Amount
    ,EvolutionCreditAccount
    ,EvolutionDebitAccount
    ,Completed
FROM
    ECS_PayRollTableView
where Completed=1

Try the below SQL Query -

DECLARE @TranDate   DATETIME = ''02/23/2013 12:00:00 AM'', -- new date time for the duplicate records
        @Amount MONEY,
        @EvolutionCreditAccount VARCHAR(MAX),
        @EvolutionDebitAccount VARCHAR(MAX),
        @Id INT = 1,
        @MaxId INT 

DECLARE @TEMPTABLE TABLE (Id INT IDENTITY(1,1),TranDate DATETIME, Amount    MONEY, EvolutionCreditAccount   NVARCHAR(MAX), EvolutionDebitAccount    NVARCHAR(MAX), Completed BIT)


INSERT @TEMPTABLE 
SELECT * FROM temp 
WHERE Completed = 1

SELECT  @MaxId = MAX(Id) FROM @TEMPTABLE

WHILE @Id <= @MaxId
	BEGIN 
	 SELECT 
		@Amount = Amount, 
		@EvolutionCreditAccount =  EvolutionCreditAccount,
		@EvolutionDebitAccount = EvolutionDebitAccount
	 FROM @TEMPTABLE
	 WHERE Id = @Id 	
	
	 
	 INSERT INTO Temp
	 (
	 	TranDate,
	 	Amount,
	 	EvolutionCreditAccount,
	 	EvolutionDebitAccount,
	 	Completed
	 )
	 VALUES
	 (
	 	@TranDate,
	 	@Amount,
	 	@EvolutionCreditAccount,
	 	@EvolutionDebitAccount,
	 	1
	 )
	  SET @Id = @Id + 1
	  
	END  

这篇关于具有一个不同列值的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！