Selecting, inserting, updating and deleting in a single query - possibly? I am new to SQL Server and have only very basic knowledge in SQL. Is it possible to create a single SQL query out of the following:


Select all rows where movedTo is not empty
Copy all these entries one by one into another table, but copying only a few columns as the other table does not have the same structure (let's assume that serial and reason are the same and movedTo should be inserted into destination
Now overwrite the region column in the prior selected results with the content of the each movedTo column row by row
Now empty the movedTo and e.g. movedDate column of all at the beginning selected rows.


I know how to do it in NoSQL, as I would just use for-loops, variables, JSON arrays and stuff, but I am lost realising it in SQL.

Hope someone can help me out on this.
Sure, you can use the OUTPUT clause in an UPDATE statement.

-- create some test tables

create table #sample(
    id int identity(1,1) not null primary key, 
    serial varchar(50), 
    reason varchar(50), 
    movedTo varchar(50), 
    region varchar(50), 
    movedDate date
    );

go

create table #sample2(
    id int identity(1,1) not null primary key, 
    serial varchar(50), 
    reason varchar(50), 
    destination varchar(50), 
    );

go

-- add some sample data
insert #sample(serial, reason, movedTo, region, movedDate)
select top(10) RTRIM(row_number() over( order by @@SPID)), newid(), newid(), null, GETDATE()
from sys.columns a;

go

-- perform the update/insert in one
update #sample 
set region = movedTo, movedTo = null, movedDate = null
output deleted.serial, deleted.reason, deleted.movedTo 
into #sample2(serial, reason, destination)
where movedTo is not null;

go

-- show the result
select *
from #sample;

select *
from #sample2;