May 3, 2012 at 8:33 am
i have table in 2 databases.
tbl1
f1 char(2)
f2 char(6)
f3 char(1)
f4 char(18)
f5 smallint
f6 thru f12 various data types
f13 identity field
f1 thru f5 are used in an index (unique).
so i tried using the following to insert the missing records into database 1 from database 2.
insert into db1.dbo.tbl1
(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12)
(select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12 from db2.dbo.tbl1
where (select a.f1+a.f2+a.f3+a.f4+cast(a.f5 as char(2)) from db2.dbo.tbl1 a
not in (select b.f1+b.f2+b.f3+b.f4+cast(b.f5 as char(2)) from db1.dbo.tbl1 b))
but this doesn't work like i thought it would i keep getting 'multiple results from subquery not allowed' type error.
what am i overlooking?
May 3, 2012 at 9:05 am
Looks like you are missing a paren in your subquery before the NOT IN
May 3, 2012 at 9:12 am
roy.tollison (5/3/2012)
i have table in 2 databases.tbl1
f1 char(2)
f2 char(6)
f3 char(1)
f4 char(18)
f5 smallint
f6 thru f12 various data types
f13 identity field
f1 thru f5 are used in an index (unique).
so i tried using the following to insert the missing records into database 1 from database 2.
insert into db1.dbo.tbl1
(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12)
(select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12 from db2.dbo.tbl1
where (select a.f1+a.f2+a.f3+a.f4+cast(a.f5 as char(2)) from db2.dbo.tbl1 a
not in (select b.f1+b.f2+b.f3+b.f4+cast(b.f5 as char(2)) from db1.dbo.tbl1 b))
but this doesn't work like i thought it would i keep getting 'multiple results from subquery not allowed' type error.
what am i overlooking?
I think this is what you're after: -
INSERT INTO db1.dbo.tbl1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12)
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12
FROM db2.dbo.tbl1
EXCEPT
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12
FROM db1.dbo.tbl1;
May 3, 2012 at 9:25 am
ok that is a typo on this forum when run the syntax check on the query it says 'Command(s) completed successfully.' but when i run it i get
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
here is the actual script:
insert into LIVEDB.dbo.APHEXPF
(aph_comp, aph_vend, aph_type, aph_id, aph_padd, aph_acct, aph_post_co,
aph_desc, aph_amt, aph_proj, aph_pline)
select c.aph_comp, c.aph_vend, c.aph_type, c.aph_id, c.aph_padd, c.aph_acct, c.aph_post_co,
c.aph_desc, c.aph_amt, c.aph_proj, c.aph_pline from TESTDB.dbo.APHEXPF c
where (select (b.aph_comp + b.aph_vend + b.aph_type + b.aph_id + cast(b.aph_padd as char(2)))
from LIVEDB.dbo.APHEXPF b) not in
(select (a.aph_comp + a.aph_vend + a.aph_type + a.aph_id + cast(a.aph_padd as char(2)))
from TESTDB.dbo.APHEXPF a)
May 3, 2012 at 9:33 am
Tried using the except verb but got:
insert into LIVEDB.dbo.APHEXPF
(aph_comp, aph_vend, aph_type, aph_id, aph_padd, aph_acct, aph_post_co,
aph_desc, aph_amt, aph_proj, aph_pline)
select c.aph_comp, c.aph_vend, c.aph_type, c.aph_id, c.aph_padd, c.aph_acct, c.aph_post_co,
c.aph_desc, c.aph_amt, c.aph_proj, c.aph_pline from TESTDB.dbo.APHEXPF c
except
select d.aph_comp, d.aph_vend, d.aph_type, d.aph_id, d.aph_padd, d.aph_acct, d.aph_post_co,
d.aph_desc, d.aph_amt, d.aph_proj, d.aph_pline from LIVEDB.dbo.APHEXPF d
with this error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.APHEXPF' with unique index 'IAPHEXPF0'.
The statement has been terminated.
May 3, 2012 at 9:39 am
Your subquery 1 after the WHERE clause looks to be pulling lots of data for the NOT IN. I think that's your problem. You need to build some join so that the subqery only returns one row.
May 3, 2012 at 9:41 am
OK sorry for posting but i got it figured out... (eliminated the select from the where clause...)
insert into LIVEDB.dbo.APHEXPF
(aph_comp, aph_vend, aph_type, aph_id, aph_padd, aph_acct, aph_post_co,
aph_desc, aph_amt, aph_proj, aph_pline)
select b.aph_comp, b.aph_vend, b.aph_type, b.aph_id, b.aph_padd, b.aph_acct, b.aph_post_co,
b.aph_desc, b.aph_amt, b.aph_proj, b.aph_pline from TESTDB.dbo.APHEXPF b
where (b.aph_comp + b.aph_vend + b.aph_type + b.aph_id + cast(b.aph_padd as char(2)))
not in
(select (a.aph_comp + a.aph_vend + a.aph_type + a.aph_id + cast(a.aph_padd as char(2)))
from LIVEDB.dbo.APHEXPF a)
again thanks all
May 3, 2012 at 9:46 am
Steve Jones - SSC Editor (5/3/2012)
Your subquery 1 after the WHERE clause looks to be pulling lots of data for the NOT IN. I think that's your problem. You need to build some join so that the subqery only returns one row.
or depending on the situation (hard to gauge since there is no DDL or Sample data) use a left join with an is null with your query as a derived table.
however the second insert error is complaining that something you are trying to insert that has a unique constraint (there can be only one in the table) is all ready there.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 9:46 am
Steve Jones - SSC Editor (5/3/2012)
Your subquery 1 after the WHERE clause looks to be pulling lots of data for the NOT IN. I think that's your problem. You need to build some join so that the subqery only returns one row.
Yeah that was it. Was overthinking it. thanks for input.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply