CODEWITHSUNDEEP
sqlsql-server
Hotlansy Httlandy
Hotlansy Httlandy

Reputation: 349

SQL Server NULL Values with an Insert statement

I have a query that is not doing what I want, I am not sure how to solve this: 

DECLARE @RoomMap TABLE
    (ID int IDENTITY(1,1),
    SourceRoom int,--> SourceRoomID
    SourceSiteID int,
    TargetRoom int, -->DemoRoomID
    TargetSiteID int
    )

INSERT INTO @RoomMap
(SourceRoom, SourceSiteID)
SELECT tblControls_Rooms.ID, @origSiteID
FROM tblControls_Rooms
WHERE SiteID = @OrigSiteID

INSERT INTO @RoomMap
(TargetRoom, TargetSiteID)
SELECT tblControls_Rooms.ID, @NewSiteID
FROM tblControls_Rooms
WHERE SiteID = @newSiteID

INSERT INTO DemoRoomMap 
(DemoRoomID, SourceRoomID)
SELECT TargetRoom, SourceRoom
FROM @RoomMap

THIS is the DemoRoomMap table when I run it:

TargetRoom  SourceRoom
    332 2
    333 3
    334 4
    335 5
    336 6
    337 9
    338 10

The result when I run the above query:

TargetRoom  SourceRoom
NULL    1942
NULL    1943
NULL    1944
NULL    1945
NULL    1946
2025    NULL
2026    NULL
2027    NULL
2028    NULL

As you can see, there are NULL values which I really do not want to insert! How can I get rid of them?

Upvotes: 1

Views: 116

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

DECLARE @RoomMap TABLE
(ID int IDENTITY(1,1),
SourceRoom int,--> SourceRoomID
SourceSiteID int,
TargetRoom int, -->DemoRoomID
TargetSiteID int
)

INSERT INTO @RoomMap
(SourceRoom, SourceSiteID, TargetRoom TargetSiteID )
SELECT S.ID, @origSiteID, T.ID, @NewSiteID
FROM 
(
    SELECT ID FROM tblControls_Rooms WHERE SiteID = @OrigSiteID
) S
CROSS JOIN
(
    SELECT ID FROM tblControls_Rooms WHERE SiteID = @NewSiteID
) T

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

You can get rid of them by inserting a value into them. See, this statement doesn't insert a value into TargetRoom:

INSERT INTO @RoomMap
(SourceRoom, SourceSiteID)
SELECT tblControls_Rooms.ID, @origSiteID
FROM tblControls_Rooms
WHERE SiteID = @OrigSiteID

Further, the subsequent statement doesn't insert values into SourceRoom:

INSERT INTO @RoomMap
(TargetRoom, TargetSiteID)
SELECT tblControls_Rooms.ID, @NewSiteID
FROM tblControls_Rooms
WHERE SiteID = @newSiteID

So, when you're done, you get a set of rows without TargetRoom and a set of rows without SourceRoom.

Upvotes: 2

Related Questions