Query about select statement for insert missing records
Hello,
I want to detect the missing recordings in a table of destination by means of a select minus or where not in but tables have no same structure and I shall wish to insert what misses:
insert into dbtarget.schematarget.tabl
select * ,
(
select name
From [ serversource ] .dbsource.sys.schemas
Objectpropertyex (object_id (' 01A.ACES ') where schema_id = ', SchemaId ')
) as folder from [serversource] .dbsource.01A.ACES
Minus
select * from dbtarget.schematarget.tabl
How can do this, do you have an example?
Thanks
bibi
I want to detect the missing recordings in a table of destination by means of a select minus or where not in but tables have no same structure and I shall wish to insert what misses:
insert into dbtarget.schematarget.tabl
select * ,
(
select name
From [ serversource ] .dbsource.sys.schemas
Objectpropertyex (object_id (' 01A.ACES ') where schema_id = ', SchemaId ')
) as folder from [serversource] .dbsource.01A.ACES
Minus
select * from dbtarget.schematarget.tabl
How can do this, do you have an example?
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what are the columns in the target table?
ASKER
folder, it's the reason for that I add select name
From [ serversource ] .dbsource.sys.schemas
Objectpropertyex (object_id (' 01A.ACES ') where schema_id = ', SchemaId ')
and a column in a source table has for name rowid and in the target table drowid.
Thanks
bibi
From [ serversource ] .dbsource.sys.schemas
Objectpropertyex (object_id (' 01A.ACES ') where schema_id = ', SchemaId ')
and a column in a source table has for name rowid and in the target table drowid.
Thanks
bibi
Didn't get you exactly. Could your explain with some sample data?
ASKER
I think I can create a temp table for add schema name and following do
insert into dbtarget.schematarget.tabl
select s.[ROW], o.[ROWID_0] from stmt_repl s
INNER JOIN ods.ODS.GAC o
on s.[ROW]=o.[ROWID_0]
and o.[DOS_0] = '01A'
where s.[ROW] not in (select o.ROWID_0 from ods.ODS.GAC)
Thanks
bibi
insert into dbtarget.schematarget.tabl
select s.[ROW], o.[ROWID_0] from stmt_repl s
INNER JOIN ods.ODS.GAC o
on s.[ROW]=o.[ROWID_0]
and o.[DOS_0] = '01A'
where s.[ROW] not in (select o.ROWID_0 from ods.ODS.GAC)
Thanks
bibi
ASKER
from this example, I can write query after creating a table temp thanks bibi
ASKER