Insert and update within same table

Question

Insert and update within same table

Brendan Hager 21

Hello,

I need to copy rows within a table with specific types and dates, insert them back in to the same table, while updating the types and dates of the inserted rows.

Below is an example table.

The rows highlighted in yellow are the existing rows, and the rows highlighted in blue should be the new rows.

Any help with the SQL is appreciated!

MelissaMa-MSFT 24,221 Reputation points

2021-05-25T02:25:22.28+00:00

Hi @Brendan Hager ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Accepted answer

1 additional answer

Your answer

MelissaMa-MSFT 24,221 Reputation points

2021-05-25T02:25:22.28+00:00

Hi @Brendan Hager ,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

INSERT Table1(Project, Type, Date)
   SELECT Project, 'B', dateadd(MONTH, 1, Date)
   FROM  Table1

That's all I can make out from the information you have given.

Brendan Hager 21 Reputation points

2021-05-22T19:03:47.113+00:00

Thank you! I'll give that a try. I could have been more specific about the date I'll be inserting. It won't be incremental although it looks like it is from my example.

Without taking too much of your time; how would I tweak that code if I wanted to insert all columns instead of naming them explicitly? The actual data has many columns, but I only need to update the rows for 2 columns, like in my example.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-22T19:12:55.733+00:00

You need to list all columns. If you don't want to type them all, you can find the table in Object Explorer, expand it, and then drag the Columns node to the query window. You can also run sp_help on the table, and select the first column in the grid and copy that.
Brendan Hager 21 Reputation points

2021-05-25T16:26:43.02+00:00

Thanks for all your help here. I only had to make one small tweak and that was probably because I'm using Redshift. I had to add "into" after INSERT.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-05-25T21:17:43.11+00:00

In such case, you can count yourself lucky. I would not have expected dateadd to work on platforms outside SQL Server, but I've been wrong before

Anyway, next time, find a Redshift forum for your question. There are substantial differences in what syntax constructs that various platform supports, although they all support a core of the SQL standard. (Of which one item certainly is that the INTO keyword is mandatory after INSERT. But in SQL Server...)
Brendan Hager 21 Reputation points

2021-05-25T21:56:14.063+00:00

I didn’t use dateadd. Only the dates within my example were incremental.

Thanks again...

Answer 2

Hi @Brendan Hager ,

Welcome to Microsoft Q&A!

Please also refer below example:

drop table if exists Table1  
  
create table Table1  
(project int,  
Type varchar(10),  
Date date,  
Col1 varchar(10),  
Col2 decimal(8,2))  
  
insert into Table1 values  
(1,'A','05-01-2021','ABC',12.51),  
(2,'A','05-01-2021','ABC',12.51),  
(3,'A','05-01-2021','ABC',12.51)  
  
insert into Table1   
select project,'B',DATEADD(DD,1,date),col1,Col2 from Table1  
where type='A'  
  
select * from Table1

Output:

project	Type	Date	Col1	Col2  
1	A	2021-05-01	ABC	12.51  
2	A	2021-05-01	ABC	12.51  
3	A	2021-05-01	ABC	12.51  
1	B	2021-05-02	ABC	12.51  
2	B	2021-05-02	ABC	12.51  
3	B	2021-05-02	ABC	12.51

If you would like to list all columns, you could refer below:

SQL Server 2017 and later:

select string_agg(COLUMN_NAME,',') from information_schema.columns where table_name = 'Table1'

SQL Server 2016 and earlier:

select stuff(( select ','+ COLUMN_NAME from information_schema.columns where table_name = 'Table1'   
 FOR XML PATH('') ), 1, 1, '')

Output:

project,Type,Date,Col1,Col2

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Insert and update within same table

1 additional answer

Your answer