Rubic INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rubic INSERT

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 09:03:57

Don't ask me why this is a "Rubic INSERT" question. "Tricky INSERT" sounds dull.

I've got 15 feedback parameters passed to a stored procedure, they're all NULL or some VARCHAR in length.

In the procedure, I could have 15 INSERT statements, each testing if the Feedback was NULL and skipping the INSERT if so...

But that would be easy.

Can this be done in a single INSERT skipping the NULL parameters?

INSERT INTO MyTable (Feedback)
SELECT @Feedback1
UNION ALL
SELECT @Feedback2
UNION ALL
SELECT @Feedback3

.... etc

UNION ALL
SELECT @Feedback15

...

I may have figured this out right here. Nest the select and elminate the NULLS?

INSERT INTO MyTable (Feedback)
SELECT Feedback FROM (
SELECT @Feedback1 AS Feedback
UNION ALL
SELECT @Feedback2
UNION ALL
SELECT @Feedback3

.... etc

UNION ALL
SELECT @Feedback15
) X
WHERE Feedback IS NOT NULL

Does it get any better than this?

Sam

Edited by - SamC on 06/11/2003 10:38:04 - highlighted the inner select

Edited by - SamC on 06/11/2003 10:40:20

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-11 : 09:37:37

Sam,

What's to prevent to values from being not null?

Doesn't sound good...


INSERT INTO myTABLE(FeedBack)
SELECT CASE WHEN @Feedback1 IS NOT NULL THEN @Feedback1
            WHEN @Feedback2 IS NOT NULL THEN @Feedback2
            WHEN @Feedback3 IS NOT NULL THEN @Feedback3
..ect
       END

1st one Wins!

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 10:37:29

Hi Brett,

I need all the feedbacks (1 through 15) INSERTed except the NULL Feedbacks.

Each Feedback must be INSERTed as another row in MyTable.

Don't want the NULL Feedbacks. I couldn't figure a way to use CASE, and the example in your post was similar to my first CASE attempt. I stopped chasing CASE since the it inserts multiple columns in a single row.

I need a row for each NOT NULL Feedback.

Sam

The 2nd INSERT I posted in the first thread eliminates NULLs with the WHERE statement...

Edited by - SamC on 06/11/2003 10:39:09

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-11 : 12:21:00

Oh Ok...

You're code should work then..no?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 13:15:52

Does this work? (Haven't tested)

INSERT INTO MyTable (Feedback)
SELECT @Feedback1 WHERE @Feedback1 is not null
UNION ALL
SELECT @Feedback2 WHERE @Feedback2 is not null
UNION ALL
SELECT @Feedback3 WHERE @Feedback3 is not null

.... etc

UNION ALL
SELECT @Feedback15 WHERE @Feedback15 is not null

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-11 : 14:21:42

Thanks Brett and Jeff,

I got the query I posted in the first thread to work.

Jeff's should work too, since I think no records are selected if an element is NULL.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources