| Author |
Topic |
|
agarwasa2008
Posting Yak Master
109 Posts |
 Posted - 2009-12-15 : 16:10:15
|
| I have an insert statement and the values are coming from different sources as follows:From a column in another table, from @Parameter or a fixed assignment. Its illustrated in the example. Any suggestions as to How I can do this?insert into table1 (col1_identity, col2, col3, col4, col5)select(@id, null, @col3, a.productName, b.productType) from [NOTHING ????????????????????] -- Please note I don't have a from table. THats the issue!!!!inner join table2 a on a.col4=@col4inner join table3 b on b.col5=@col5Thanks,SA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-15 : 16:13:21
|
| insert into table1 (col1_identity, col2, col3, col4, col5)select @id, null, @col3, a.productName, b.productTypefrom table2 a on a.col4=@col4join table3 b on b.col5=@col5Are @id, @col3, @col4, and @col5 parameters or columns in one of the tables? It is very unclear.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 16:14:53
|
| Thanks tkizer. These are parameters!SA |
|
|
|
goodsolution
Starting Member
38 Posts |
Posted - 2009-12-15 : 16:20:18
|
| You have to write something like thisdecalre @col3 varchar(10) set @col3 = (select statement)decalre @col5 varchar(10) set @col5 = (select statement)EXEC ('insert into table1 (col2, col3, col4, col5)select '''', '+@col3+', t1.productname, b.prodcutype from table1 T1 inner join table2 T2 on t1.col4 = '+@col4+'innert join table3 T3 ont3.col5 = '+@col5+'')-Thanks |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 16:34:46
|
| select '''' ==> a null value with the surrounding open and close apostrophe. Did I get that Right?SA |
|
|
|
goodsolution
Starting Member
38 Posts |
Posted - 2009-12-15 : 16:36:37
|
| Yes that is for null value. because we are writing EXEC ('') we need to keep '' ''.-Thanks |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 16:45:51
|
| This is the error msg (as indicated below). Obviously the line # are different. But if you can help me decipher the issue it would really help me. I know I am close to the solution at this point.Error Message:Msg 102, Level 15, State 1, Procedure publicCompanyStoredProcedure, Line 5Incorrect syntax near '@'.Msg 137, Level 15, State 2, Procedure publicCompanyStoredProcedure, Line 19Must declare the scalar variable "@col3".SA |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-15 : 16:56:20
|
| Please post the entire code of your procedure. |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 16:57:49
|
| okay I finally figured out the error msg issue. Col2 which I have assigned to null is actually: FK, char(8),not null?Please advice how to deal with itSA |
|
|
|
goodsolution
Starting Member
38 Posts |
Posted - 2009-12-15 : 17:51:13
|
| so insert it with the right value instead of inserting wiht null, if you are getting null from the parameter, you need to write ISNULL(@COL3, VALUE) function. if any value is OK, then use ISNULL(@COL3, -1). -Thanks |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 17:56:10
|
| Thanks! SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-15 : 18:35:15
|
quote:
Originally posted by goodsolution
You have to write something like thisdecalre @col3 varchar(10) set @col3 = (select statement)decalre @col5 varchar(10) set @col5 = (select statement)EXEC ('insert into table1 (col2, col3, col4, col5)select '''', '+@col3+', t1.productname, b.prodcutype from table1 T1 inner join table2 T2 on t1.col4 = '+@col4+'innert join table3 T3 ont3.col5 = '+@col5+'')-Thanks
Do not use dynamic SQL for this. It is very unnecessary, a performance issue, and a security issue.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-12-15 : 19:41:12
|
Thanks tkizer. I followed your suggestions and it worked very well. Thank you Very Much!! It is something I had never used before hence the issue.SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
insert into statement