MSSQL insert with nested statement

Question

   insert into SDORGDESCGL(COMP_CD ,ORG_CODE) 
            values ('1',(select (select case when '60'='60' then '0001' when '60' = '61' then '0002' end)+'00000000000000000000000000000000'))

------New Edit-----
*i have simplified out the main issue.The second values data input error.

My question is: Why do I get the following error? When I compile each statement separately (every Values statement) it works fine.

error:
Msg 1046, Level 15, State 1, Line 2 Subqueries are not allowed in this context. Only scalar expressions are allowed.

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '+'.

I'm sure if you formatted your sql you'd find it easier to create this. Also, you want an insert select statement if you are looking to insert more than one value at a time. — crthompson
– crthompson, Commented Aug 27, 2014 at 5:39
i put all these thing at 1 row is because i will put it in .txt file, but i not sure what is the error come out =( , can you sore me some clue perhaps? — Jelly
– Jelly, Commented Aug 27, 2014 at 5:43
The error means that you are selecting multiple records and trying to insert them into a single cell. — crthompson
– crthompson, Commented Aug 27, 2014 at 5:45
@paqogomez i ald make some edit to the question, would you show me how to solved this?Thanks. — Jelly
– Jelly, Commented Sep 2, 2014 at 4:04

nshah · Accepted Answer · 2014-09-02 13:11:35Z

2

Edit: Your query works in SQL Serve 2008 R2. Either your configuration is different or you have a different version of SQL Server.

If you have an older version you can try the following:

 insert into #test(COMP_CD ,ORG_CODE) 
            Select '1',(select (select case when '60'='60' then '0001' when '60' = '61' then '0002' end)+ ('00000000000000000000000000000000'))

Original answer:

IF OBJECT_ID('tempdb.dbo.#test') IS NOT NULL
BEGIN
 DROP TABLE #test
END

CREATE TABLE #test 
            (comp_cd VARCHAR(1000), 
             org_code VARCHAR(1000), 
             node_desc VARCHAR(1000), 
             parentorg_cd VARCHAR(1000), 
             user_id VARCHAR(1000), 
             rec_mode VARCHAR(1000)) 

INSERT INTO #test
            (comp_cd, 
             org_code, 
             node_desc, 
             parentorg_cd, 
             user_id, 
             rec_mode) 

OUTPUT INSERTED.*

VALUES      ('1', 
             (SELECT (SELECT LV1 = CASE 
                                     WHEN '60' = '60' THEN '0005' 
                                     WHEN '60' = '61' THEN '0002' 
                                   END) 
                     + (SELECT RIGHT('0000' + CONVERT(VARCHAR, (SELECT(SELECT 
                                     TOP 1 Substring 
                                     ( 
                                     org_code, 
                                       5, 4) 
                                               AS SUB 
                                               FROM #test WHERE comp_cd = 
                                     1 ORDER BY 
                                     sub DESC)+ 
                                     1) 
                                       ), 4)) 
                     + '0000000000000000000000000000'), 
             (SELECT '822000' + ' - ' 
                     + 'Receiving & W/house - Selangor'), 
             (SELECT (SELECT CASE 
                               WHEN '60' = '60' THEN '0001' 
                               WHEN '60' = '61' THEN '0002' 
                             END) 
                     + '00000000000000000000000000000000'), 
             'SMHIGWN', 
             'A')

edited Sep 2, 2014 at 13:11

answered Aug 27, 2014 at 6:01

nshah

3501 silver badge5 bronze badges

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

2 Comments

nshah Over a year ago

Put your query into Notepad++ and then go to menu View->Show Symbol->Show all characters. See if there are any strange characters in your query. Or, since your query is small, delete all whitespace and re-add the spaces.

crthompson Over a year ago

Why would '60' ever equal '61'?!?

Lmu92 · Accepted Answer · 2014-08-27 06:08:47Z

1

I try to reformat and rewrite your code to be at least syntactically correct:

with cte as
(
    SELECT top 1 SUBSTRING(org_code,5,4) AS SUB
    FROM SDORGDESCGL
    WHERE COMP_CD = 1
    ORDER BY SUB DESC
)
INSERT INTO SDORGDESCGL(COMP_CD ,ORG_CODE,NODE_DESC,PARENTORG_CD,USER_ID,REC_MODE)
SELECT
    '1',
    CASE WHEN '60' = '60' THEN '0005' WHEN '60' = '61' THEN '0002' END
       + RIGHT('0000'+ CONVERT(VARCHAR,SUB+1)),4) + '0000000000000000000000000000',
    '822000'+' - '+'Receiving & W/house - Selangor',
    CASE WHEN '60'='60' THEN '0001' WHEN '60' = '61' THEN '0002' END +'00000000000000000000000000000000',
    'SMHIGWN',
    'A'
FROM cte

However, the logic doesn't seem to be right, since the two CASE statements will always return true (due to '60'='60')....

answered Aug 27, 2014 at 6:08

Lmu92

9525 silver badges5 bronze badges

1 Comment

Jelly Over a year ago

yea i purposely set the static data for easy testing, sorry for not mention it out.

Collectives™ on Stack Overflow

MSSQL insert with nested 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

Related