use tempdb
go
drop table child
drop table parent
drop table SourceTable
go
create table parent (parentId int not null primary key)
create table child (childid int identity(1,1), parentId int not null,strCol varchar(10),unqCol int unique, intCol int, numCol numeric(2,1),tinyintCol tinyint, )
alter table child add constraint fk_child foreign key (parentId) references parent(parentId)
go
insert into parent (parentId)
values(1)
go
declare @bigint as bigint = pOWER(cast(2 as bigint),32)
select *
into SourceTable
from (
values
(1,replicate('A',11),1,100,10.22,256)
,(1,replicate('A',10),1,100,10.22,256)
,(1,replicate('A',10),2,100,10.22,256)
,(1,replicate('A',10),3,100,1.2,256)
,(1,replicate('A',10),3,100,1.2,256)
,(1,replicate('A',10),3,@bigint,1.2,255)
,(1,replicate('A',10),3,@bigint,1.2,255)
,(1,replicate('A',10),3,@bigint,1.2,255)
,(1,replicate('A',10),4,100,1.2,255)
,(1,replicate('A',10),4,100,1.2,255)
,(null,replicate('A',10),9,100,1.2,255)
,(2,replicate('A',10),10,@bigint,1.2,255)
,(2,replicate('A',10),5,100,1.2,255)
,(2,replicate('A',10),6,100,1.2,255)
,(1,replicate('A',10),11,@bigint,1.2,255)
,(1,replicate('A',10),7,100,1.2,255)
,(1,replicate('A',10),8,100,1.2,255)
) v(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
go
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
go
print ' '
print 'Exclude varchar values that will be truncated'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
print ' '
print ' Exclude numeric values that are too big'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
print ' '
print 'Exclude values too big for tinyint'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
print ' '
print 'Exclude values too big for int'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
and (intCol is null or try_convert(int,intCol ) is not null)
print ' '
print 'Exclude specific value that breaks unique constraint'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
and (intCol is null or try_convert(int,intCol ) is not null)
and unqcol <> 3
print ' '
print 'Exclude values that break unique constraint'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
and (intCol is null or try_convert(int,intCol ) is not null)
and not exists (select 1 from child c where c.unqCol = SourceTable.unqcol)
and (select COUNT(1) from sourcetable s where s.unqCol = SourceTable.unqcol) <=1
go
print ' '
print 'Exclude values that break foreign key constraint'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
and (intCol is null or try_convert(int,intCol ) is not null)
and not exists (select 1 from child c where c.unqCol = SourceTable.unqcol)
and (select COUNT(1) from sourcetable s where s.unqCol = SourceTable.unqcol) <=1
and (parentId is null or
exists (select 1 from parent p where p.parentId = SourceTable.parentId))
go
print ' '
print 'Exclude values that breaks not null constraint'
insert into child(parentId, strcol,unqcol, intCol, numCol,tinyintCol)
select parentId, strcol,unqcol, intCol, numCol,tinyintCol
from SourceTable
where len(strcol) <= 10
and (numCol is null or try_convert(numeric(2,1),numCol) is not null)
and (tinyintCol is null or try_convert(tinyint,tinyintCol) is not null)
and (intCol is null or try_convert(int,intCol ) is not null)
and not exists (select 1 from child c where c.unqCol = SourceTable.unqcol)
and (select COUNT(1) from sourcetable s where s.unqCol = SourceTable.unqcol) <=1
and (parentId is not null and
exists (select 1 from parent p where p.parentId = SourceTable.parentId))