Categories
How To script sql

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- NOTE: This is an EXAMPLE to show it is possible
-- to achieve moving rows using an implicit transaction
-- from one table into another table.

-- Set up example tables, Foo & Bar.
drop table if exists [dbo].[Bar];
drop table if exists [dbo].[Foo];

create table [dbo].[Bar](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] null index ix_mycol
)

create table [dbo].[Foo](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] index ix_mycol,
)
GO

-- Create some dummy data
insert into [dbo].[Foo]( [MyKey], [MyColumn] )
values (1, 11), (2, 22), (3, 33), (4, 44);
GO 10240

-- Verify the rows before.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

-- Move rows from table Foo into table Bar using two styles.

-- Method 1: "Direct" move
delete
from [dbo].[Foo]
output [deleted].[MyKey], [deleted].[MyColumn]
into [dbo].[Bar]( [MyKey], [MyColumn] )
where [ID] % 3 = 1;


-- Method 2: "Subquery" move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [ID] % 3 = 2
) [MovedRows]
where [MovedRows].[MyKey] = 2;
-- Note: The last where is not needed, just showing it is possible to re-filter incoming [from the deleted output] rows.

-- Verify the counts after.
-- Note: Any deleted rows filtered out via Method 2 are now gone.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

Categories
dos

RoboCopy – Exclude existing files.cmd

robocopy.exe c:Sourcepath c:Destpath /E /XC /XN /XO

/E makes Robocopy recursively copy subdirectories, including empty ones.
/XC excludes existing files with the same timestamp, but different file sizes. Robocopy normally overwrites those.
/XN excludes existing files newer than the copy in the source directory. Robocopy normally overwrites those.
/XO excludes existing files older than the copy in the source directory. Robocopy normally overwrites those.

With the Changed, Older, and Newer classes excluded, Robocopy will exclude files existing in the destination directory.