SQL-在同一表和增量版本中复制数据 [英] SQL - copy data within same table and increment version

问题描述

我将 MS SQL 与 SSIS 结合使用，在这里我需要复制和粘贴表的行(相同表用于源和目标) )，同时还增加"subversion"列.

I use MS SQL in combination with SSIS where I need to copy and paste rows of a table (same table for source and destination) for a given DocumentID while also incrementing the "subversion" column.

示例:

DocumentID=123 | Location=a | Version=2 |Subversion=4
DocumentID=123 | Location=b | Version=2 |Subversion=4
DocumentID=123 | Location=c | Version=2 |Subversion=4

复制后:

DocumentID=123 | Location=a | Version=2 |Subversion=5
DocumentID=123 | Location=b | Version=2 |Subversion=5
DocumentID=123 | Location=c | Version=2 |Subversion=5

在SSIS中，我已经将max(Subversion)(在示例中从4增加到5)增加了. 我需要的是一条select/insert语句来复制数据.

Within SSIS I already increment the max(Subversion) (from 4 to 5 in the example). What I need is a select/insert statement to copy the data.

由于此表有大约150列，我希望找到一种不列出所有列的方法，但是即使有可能，我也找不到任何信息.

Since this table has around 150 columns I was hoping for a way without listing all columns but I couldn't find any information if that is even possible.

我已经尝试过将插入"与选择"结合使用，但是它总是以错误(汇总问题)结尾.

I've tried Insert into in combination with Select but it always ended in errors (aggregate issues).

  Insert Into MyTable (AllMyColumns)
  select (AllmyColumns)
  from MyTable
  where DocumentID =123 AND ...

我该如何构建此语句?

感谢您的帮助.

推荐答案

我认为以下解决方法将为您提供帮助.

I think following workaround will help you.

--Move incremental rows to the #temp table
Select * into #temp from MyTable where DocumentID=123 and ......

Declare @version varchar(20)

Select @version = Max(SubVersion) from #temp

--To increment the value
set @version='Subversion='+Cast(Substring(@version,charindex('=',@version)+1,len(@version)) as int)+1

--To update the value into temp
update #temp set SubVersion=@version

--Now insert into your table
insert into MyTable
Select * from #temp

--drop the temp table
drop table #temp

希望这会有所帮助

这篇关于SQL-在同一表和增量版本中复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！