3

I am trying to insert the records that come of this query into a temp table. Any help or suggestion will be helpful

insert into #AddRec
select *
from stg di
right join
ED bp
on
bp.ID = di.ID
except
select *
from stg di
inner join
ED bp
on
bp.ID = di.ID
Ian Kenney
  • 6,376
  • 1
  • 25
  • 44
knahs
  • 65
  • 6
  • 12
  • #AddRec is it a tablename with # ? – zod Apr 26 '13 at 23:40
  • can you post a little more information - which database server sql server 2012? what is the schema of #AddRec and of the other 2 tables – Ian Kenney Apr 26 '13 at 23:44
  • http://stackoverflow.com/questions/10656088/how-to-insert-into-select-but-one-field-is-from-somewhere-else – zod Apr 26 '13 at 23:45

4 Answers4

4

This may help it simplifies your query a little.

create table #AddRec(id int) ;

insert into #addrec
select  ed.id
from stg right join 
ed on stg.id=ed.id 
where stg.id is null;

select * from #Addrec

If you need more fields from the tables add the definitions into the temp table and add them into the select line

Ian Kenney
  • 6,376
  • 1
  • 25
  • 44
1
;WITH Q AS
(
select *
from stg di
right join
ED bp
on
bp.ID = di.ID
except
select *
from stg di
inner join
ED bp
on
bp.ID = di.ID 
)
INSERT INTO #AddRec(... list of fields ...)
SELECT (... list of fields ...) FROM Q

if you want to create the temp table from scratch, just replace the insert with:

SELECT (... list of fields ...) 
INTO #AddRec
FROM Q
Dan S
  • 1,186
  • 1
  • 7
  • 12
0

if your select return matches the field count of insert table this should work!

  insert into tbl1 (field1,field2)
    select field1,field2 from.................
zod
  • 12,092
  • 24
  • 70
  • 106
0

easiest way

select *
into #AddRec
from stg di
right join ED bp on bp.ID = di.ID
except
select * from stg di
inner join ED bp on bp.ID = di.ID
user15462130
  • 1
  • 1