SQL Server Performance Forum – Threads Archive

Insert Into – structure of statement

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)[email protected]+ char(39)+’,’
+char(39)[email protected] + +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

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.

Can you please describe more about problem you are facing.

As Ranjijain Said….Explain ur problem…like it throw’s error while compilation or execution of SP.

Why do you use Dynamic SQL? Madhivanan Failing to plan is Planning to fail

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

SURYA

How about:<pre id="code"> insert into table(<column list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> select <constant1>, <constant2>, <expression based on column value>,…. from table1 t1 join table2 t2 on ….</pre id="code">

Lubo, have you solved your issue in the meantime or do you still need help? If so, please provide the requested informations to help you here.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Yep, From my understanding mmarovic is correct. This is exactly what I was going to suggest. No need to use dynamic sql. INSERT Into TABLE1 (Val1,Val2,FK_Val3) select @Val1, @Val2, some_value From TABLE1, Table2 where TABLE1.aaaaa=TABLE2.bbbbb <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mmarovic How about:<pre id="code"> insert into table(<column list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> select <constant1>, <constant2>, <expression based on column value>,…. from table1 t1 join table2 t2 on ….</pre id="code"> <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

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

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.

Mirko, i sent emaiu to you 15 min ago …

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.

set @sqlCommand=’INSERT Into TAB_IN (val,val2,val3,set_Result) Values(‘
+char(39)[email protected]+ char(39)+’,’
+char(39)+ @val2 + +char(39)+’,’
+ ‘convert(money , ‘+char(39)[email protected]+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

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.

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.

Mirko , i sent an email to [email protected]
Lubo

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.

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. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>