SQL Server NULL Values with an Insert statement -

i have query not doing want, not sure how solve this:

declare @roommap table     (id int identity(1,1),     sourceroom int,--> sourceroomid     sourcesiteid int,     targetroom int, -->demoroomid     targetsiteid int     )  insert @roommap (sourceroom, sourcesiteid) select tblcontrols_rooms.id, @origsiteid tblcontrols_rooms siteid = @origsiteid  insert @roommap (targetroom, targetsiteid) select tblcontrols_rooms.id, @newsiteid tblcontrols_rooms siteid = @newsiteid  insert demoroommap  (demoroomid, sourceroomid) select targetroom, sourceroom @roommap 

this demoroommap table when run it:

targetroom  sourceroom     332 2     333 3     334 4     335 5     336 6     337 9     338 10 

the result when run above query:

targetroom  sourceroom null    1942 null    1943 null    1944 null    1945 null    1946 2025    null 2026    null 2027    null 2028    null 

as can see, there null values not want insert! how can rid of them?

you can rid of them inserting value them. see, statement doesn't insert value targetroom:

insert @roommap (sourceroom, sourcesiteid) select tblcontrols_rooms.id, @origsiteid tblcontrols_rooms siteid = @origsiteid 

further, subsequent statement doesn't insert values sourceroom:

insert @roommap (targetroom, targetsiteid) select tblcontrols_rooms.id, @newsiteid tblcontrols_rooms siteid = @newsiteid 

so, when you're done, set of rows without targetroom , set of rows without sourceroom.