SQL - copy data within same table and increment ve

问题:

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.

Example:

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

after copy:

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

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.

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 ...

How do I need to build this statement?

thanks for your help.

回答1:

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

Hope this helps

回答2:

Since you said you need insert into select give this a try then, and add conditions as your desire..

INSERT INTO table1(docid, location, version, subversion)
SELECT  (docid, location, version, subversion)
FROM    table1
;

To give a better answer, you may need to at least show your current query and what mechanism makes the increment, table schema... I could even assume you are already at this step anyway...

回答3:

If your source and destination columns are in the sames order then just do:

INSERT INTO MyTable
SELECT *
FROM MyTable
WHERE DocumentID =123 AND ...