Insert a duplicate copy of any record that meets certain criteria

Transact-SQL
, ,
1

Sorry for this dumb question but I am very new to SQL. Could someone help me write a SQL code?

I have MS SQL 2014 server.
I Have Table “A” with 10 columns. Column 9 has any of these four values: “Test”, “Hold”, “Go”, or “Flag”

I would like to make a duplicate copy of any record where column 9 has a value = “Hold” or “Go” and change it to the value “Step 3”.

Thanks

2

Something like this perhaps:

select a.col1
      ,a.col2
      ,a.col3
      ,a.col4
      ,a.col5
      ,a.col6
      ,a.col7
      ,a.col8
      ,'Step 3'
      ,a.col10
  into yourtable
  from yourtable as a
 where a.col9 in ('Test','Hold','Go')
   and not exists (select 1
                     from yourtable as b
                    where b.col1=a.col1
                      and b.col2=a.col2
                      and b.col3=a.col3
                      and b.col4=a.col4
                      and b.col5=a.col5
                      and b.col6=a.col6
                      and b.col7=a.col7
                      and b.col8=a.col8
                      and b.col9='Step 3'
                      and b.col10=a.col10
                  )
3

Thank you bitsmed - How do you handle if the a.col1 is Primary key that is unique?

4

How is the field normally assigned a key?
Is there a sequence handling this?
Please show the table definition in the form of create statement.

5

Sorry I am not totally sure. But the table is already created from another system and they use a stored procedure to bring it to our SQL. I think it is done sequentially as I see UUID on three of the columns.

6

Thank you!

7

Maybe this:

insert
  into yourtable(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10)
select newid()
      ,a.col2
      ,a.col3
      ,a.col4
      ,a.col5
      ,a.col6
      ,a.col7
      ,a.col8
      ,'Step 3'
      ,a.col10
  from yourtable as a
 where a.col9 in ('Test','Hold','Go')
   and not exists (select 1
                     from yourtable as b
                    where b.col2=a.col2
                      and b.col3=a.col3
                      and b.col4=a.col4
                      and b.col5=a.col5
                      and b.col6=a.col6
                      and b.col7=a.col7
                      and b.col8=a.col8
                      and b.col9='Step 3'
                      and b.col10=a.col10
                  )
;

or if col1 is defined with "default newid", you can omit col1 like this:

insert
  into yourtable(col2,col3,col4,col5,col6,col7,col8,col9,col10)
select a.col2
      ,a.col3
      ,a.col4
      ,a.col5
      ,a.col6
      ,a.col7
      ,a.col8
      ,'Step 3'
      ,a.col10
  from yourtable as a
 where a.col9 in ('Test','Hold','Go')
   and not exists (select 1
                     from yourtable as b
                    where b.col2=a.col2
                      and b.col3=a.col3
                      and b.col4=a.col4
                      and b.col5=a.col5
                      and b.col6=a.col6
                      and b.col7=a.col7
                      and b.col8=a.col8
                      and b.col9='Step 3'
                      and b.col10=a.col10
                  )
;
8

hi

i know this topic is from long time ago

i tried something different

using union all
Performance for a lot of data might be an issue

if it helps great
:slight_smile:
:slight_smile:

Different SQL 
select a.col1
      ,a.col2
      ,a.col3
      ,a.col4
      ,a.col5
      ,a.col6
      ,a.col7
      ,a.col8
      ,case when a.col9 in ('Test', 'Hold', 'Go', 'Flag' ) then 'Step3' else a.col9 end 
      ,a.col10
  from yourtable as a
  union all 
 select a.col1
      ,a.col2
      ,a.col3
      ,a.col4
      ,a.col5
      ,a.col6
      ,a.col7
      ,a.col8
      ,'Step3' 
      ,a.col10
  from yourtable as a where  a.col9 in ('Test', 'Hold', 'Go', 'Flag' )
9

Thank you!