SQL Server Performance

Insert Into - structure of statement

Discussion in 'T-SQL Performance Tuning for Developers' started by luma, Dec 12, 2005.

  1. luma New Member

    HI , I am unhappy... I'm working with many dates structure. My problems are make some sql statements. One of them it's now combining Insert INto with Select.
    I need insert some value in stored procedure to some table .But still one from another table.
    My sample is :


    Set @sqlCommand='INSERT Into TABLE1 (Val1,Val2,FK_Val3) Values('
    +char(39)+@Val1+ char(39)+','
    +char(39)+@Val2 + +char(39)+','

    and value from select

    + ' Select some_value From TABLE1, Table2 where TABLE1.aaaaa=TABLE2.bbbbb '
    +char(39) +')'

    Execute (@sqlCommand)


    Structures of columns are : aaaaaa , bbbbbb ....... char(14)
    column FK_Val3 ........bigint
    column some_value .....bigint



    Many, many thanks , Lubo
  2. satya Moderator

    http://vyaskn.tripod.com/code.htm

    HTH

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ranjitjain New Member

    Can you please describe more about problem you are facing.
  4. sivaraman New Member

    As Ranjijain Said....Explain ur problem...like it throw's error while compilation or execution of SP.
  5. Madhivanan Moderator

    Why do you use Dynamic SQL?

    Madhivanan

    Failing to plan is Planning to fail
  6. vsnreddi New Member

    Question is not clear...What exactly you want..As above all are said..post it what exactly you need



    SURYA



  7. mmarovic Active Member

    How about:<pre id="code"><font face="courier" size="2" id="code"><br />insert into table(&lt;column list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />select &lt;constant1&gt;, &lt;constant2&gt;, &lt;expression based on column value&gt;,....<br />from table1 t1<br />join table2 t2 on ....</font id="code"></pre id="code">
  8. FrankKalis Moderator

    Lubo, have you solved your issue in the meantime or do you still need help? <br />If so, please provide the requested informations to help you here.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  9. SQLDBcontrol New Member

    Yep,<br /><br />From my understanding mmarovic is correct. This is exactly what I was going to suggest. No need to use dynamic sql.<br /><br />INSERT Into TABLE1 (Val1,Val2,FK_Val3) <br />select @Val1, @Val2, some_value <br />From TABLE1, Table2 <br />where TABLE1.aaaaa=TABLE2.bbbbb<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br />How about:<pre id="code"><font face="courier" size="2" id="code"><br />insert into table(&lt;column list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />select &lt;constant1&gt;, &lt;constant2&gt;, &lt;expression based on column value&gt;,....<br />from table1 t1<br />join table2 t2 on ....</font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  10. luma New Member

    Sorry, I was out of PC a long time. Your advices are OK - when i write this statement in query analyzer. But my problem is wrong syntax. During run stor proc i am getting some values . It is't problem for inserting. But one of them is result of select from other table. I wrote about it on top of article.
    Lubo
  11. mmarovic Active Member

    Maybe you can try to send me explanation by email in czech (preferable) or even Slovakian. I am afraid I didn't understand your explanations in english.
  12. luma New Member

    Mirko, i sent emaiu to you 15 min ago ...
  13. Adriaan New Member

    Okay, can you run the procedure - as a test - with this small change:

    --Execute (@sqlCommand)
    PRINT @sqlCommand

    ... and post the results here, then we'll know what could be wrong with your the way you're building the statement.

    PRINT does not execute the insert statement, so this just temporary for debugging: once we've sorted out the problem, you can restore the Execute (@sqlCommand) line.
  14. luma New Member


    set @sqlCommand='INSERT Into TAB_IN (val,val2,val3,set_Result) Values('
    +char(39)+@val1+ char(39)+','
    +char(39)+ @val2 + +char(39)+','
    + 'convert(money , '+char(39)+@val3+char(39)+')'+','
    + char(39)+' Select TAB_Out From some_value, TAB_IN where TAB_IN.val1=TAB_Out.some_Value_999 '+char(39)
    + ')'


    print (@sqlCommand)=
    INSERT Into Volanie (val1,val2,val3,set_Result)
    Values('0123456','987654',convert(money , '1.9000'),' Select some_value From TAB_Out , TAB_IN where TAB_IN.val1=TAB_OUT.some_thing ')
    Server: Msg 8114, Level 16, State 5, Line 1


    After execute
    Error converting data type varchar to bigint.

    Datatype : set_Result is bigint , some_value bigint
    TAB_IN.val1= varchar , TAB_OUT.something=varchar

    Lubo



  15. Adriaan New Member

    The names in your script seem to be messed up. At the start of the thread, you were inserting into TABLE1. Now, your dynamic SQL statement has TAB_IN, but the printed insert statement says Volanie.

    Check the data type for the columns val1, val2, val3 and set_result in table Volanie - the values you're inserting do not match.
  16. mmarovic Active Member

    I haven't recieved any email from you. Which email address did you use? I controll my yahoo email address regurarly, so you can expect the fastest answer from that email address.
  17. luma New Member

    Mirko , i sent an email to marovicmirko@hotmail.com
    Lubo
  18. mmarovic Active Member

    That account was deactivated last week for some reason. Now I activated it again, but I lost emails I had in inbox before. If you still have the same problem please send it again.

    To moderators: Sorry for this, but Lubo doesn't have contact email in his profile, so the only way to reach him is through forum.
  19. FrankKalis Moderator

    No problem. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] However, I would probably cloak the E-Mail addresses. At least it helps against automated spam attacks.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page