SQL Server NULL Values with an Insert statement

Question

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?

Saying that the query is "not doing what you want" is not much of an information. Please explain what do you want query to do, provide some sample data and the expected results. — Nenad Zivkovic
– Nenad Zivkovic, Commented Jul 31, 2013 at 14:08

Mike Perrenoud · Accepted Answer · 2013-07-31 13:51:15Z

2

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.

answered Jul 31, 2013 at 13:51

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hotlansy Httlandy Over a year ago

I am still not sure how to do it.

Mike Perrenoud Over a year ago

@HotlansyHttlandy, I can understand that. But from my perspective I answered the question at hand. I don't really know what you want because that's not clear from your question.

Giannis Paraskevopoulos · Accepted Answer · 2013-07-31 13:54:54Z

0

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

answered Jul 31, 2013 at 13:54

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

1 Comment

Hotlansy Httlandy Over a year ago

I want to the TargetRoom and SourceRoom into DemoRoomMap but I want them distinct! one source room, one targer room in the query above I am getting the following:1942 2025 1943 2025 1944 2025 1945 2025 1946 2025

Collectives™ on Stack Overflow

SQL Server NULL Values with an Insert statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest