foreign key

hi friends,
I want to check the relationship between tables before migration.
so i wrote a procedure which will push the unrelated data from the source
db(@.i_oldDB) to the error database(@.i_errorDb).
alter procedure TransactionValidation
(
@.i_oldDb varchar(100),
@.i_errorDb varchar(100),
@.i_ParentTable varchar(100),
@.i_ChildTable varchar(100),
@.i_PrimaryKey varchar(100),
@.i_ForeignKey varchar(100)
)
as
begin
Declare @.SQL nvarchar(4000)
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table ' +
@.i_errorDb + '..'+ @.i_ChildTable
exec sp_executesql @.sql
select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + ' from
'
+ @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
@.i_ChildTable + '.' + @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' +
@.i_oldDb + '..'+ @.i_ChildTable + '.'
+ @.i_ForeignKey + ' not in
(select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable + ')'
exec sp_executesql @.sql
end
now my problem is, if i have multiple relationship column in the table...
this will not work. how to do this?
its very urgent.
pls help me to solve this.
thanks
vanithaThere are a couple of alternatives to NOT IN for composite keys. You could
use NOT EXISTS or an OUTER JOIN. The generated SQL would be something like
the untested examples below.
SELECT *
INTO MyErrorTable
FROM MyChildTable
WHERE NOT EXISTS
(
SELECT *
FROM MyParentTable
WHERE MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
)
SELECT MyChildTable.*
INTO MyErrorTable
FROM MyChildTable
LEFT OUTER JOIN MyParentTable ON
MyParentTable.Col1 = MyChildTable.Col1 AND
MyParentTable.Col2 = MyChildTable.Col2
WHERE MyParentTable.Col1 IS NULL
Hope this helps.
Dan Guzman
SQL Server MVP
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:710A7736-41F8-478F-BFFF-3AA86A31FFE0@.microsoft.com...
> hi friends,
> I want to check the relationship between tables before migration.
> so i wrote a procedure which will push the unrelated data from the source
> db(@.i_oldDB) to the error database(@.i_errorDb).
> alter procedure TransactionValidation
> (
> @.i_oldDb varchar(100),
> @.i_errorDb varchar(100),
> @.i_ParentTable varchar(100),
> @.i_ChildTable varchar(100),
> @.i_PrimaryKey varchar(100),
> @.i_ForeignKey varchar(100)
> )
> as
> begin
> Declare @.SQL nvarchar(4000)
> select @.SQL = 'if exists (select * from ' + @.i_errorDb +
> '.INFORMATION_SCHEMA.TABLES where ' +
> 'Table_Name like ' + CHAR(39) + @.i_ChildTable + CHAR(39) + ') drop table '
> +
> @.i_errorDb + '..'+ @.i_ChildTable
> exec sp_executesql @.sql
> select @.sql = 'SELECT * into ' + @.i_errorDb + '..' + @.i_ChildTable + '
> from
> '
> + @.i_oldDb + '..'+ @.i_ChildTable + ' where ' + @.i_oldDb + '..'+
> @.i_ChildTable + '.' + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
>
> exec sp_executesql @.sql
> select @.sql = 'delete from ' + @.i_oldDb + '..'+ @.i_ChildTable + ' where '
> +
> @.i_oldDb + '..'+ @.i_ChildTable + '.'
> + @.i_ForeignKey + ' not in
> (select ' + @.i_PrimaryKey + ' from ' + @.i_oldDb + '..'+ @.i_ParentTable +
> ')'
> exec sp_executesql @.sql
>
> end
> now my problem is, if i have multiple relationship column in the table...
> this will not work. how to do this?
> its very urgent.
> pls help me to solve this.
> thanks
> vanitha
>