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.
| Author |
Topic |
|
requestor
Starting Member
17 Posts |
 Posted - 2007-10-12 : 04:08:53
|
| hai what is the mistake im doing in this...help pls..declare @sql varchar(8000)set @sql = 'declare @inputvalue varchar(8000)set @inputvalue = ''s1234567k,123456789123,23,24|s2234567k,223456789123,23,24|s3234567k,323456789123,23,24|''declare @separator char(1)set @separator = ''|''declare @separator_position int declare @tbl table(entityid char(30),id decimal,source smallint,reason smallint)'set @sql = @sql + 'while patindex('''+'%'''+'+'+'@separator'+ '+' +''''+ '%'''+','+'@inputvalue' +') <> 0'set @sql = @sql + 'begin 'set @sql = @sql + 'select @separator_position = patindex('''+'%'''+'+'+'@separator'+ '+' +''''+ '%'''+','+'@inputvalue' +')'set @sql = @sql + 'select @inputvalue = + left(@inputvalue, @separator_position - 1)'--set @sql = @sql + 'print @separator_position '--set @sql = @sql + 'print @inputvalue 'set @sql = @sql + 'insert into @tbl(entityid,id,source,reason) values (@inputvalue)'set @sql = @sql + 'end 'print (@sql)exec (@sql)There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 04:25:54
|
| The error looks pretty clear, what does theprint (@sql)output?Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-12 : 04:31:50
|
| Why too much dynamic sql?Make sure you read this fullywww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-12 : 04:35:44
|
quote:
Originally posted by requestor
declare @sql varchar(8000)set @sql = 'declare @inputvalue varchar(8000)set @inputvalue = ''s1234567k,123456789123,23,24|s2234567k,223456789123,23,24|s3234567k,323456789123,23,24|''declare @separator char(1)set @separator = ''|''declare @separator_position int declare @tbl table(entityid char(30),id decimal,source smallint,reason smallint)'set @sql = @sql + 'while patindex('''+'%'''+'+'+'@separator'+ '+' +''''+ '%'''+','+'@inputvalue' +') <> 0'set @sql = @sql + 'begin 'set @sql = @sql + 'select @separator_position = patindex('''+'%'''+'+'+'@separator'+ '+' +''''+ '%'''+','+'@inputvalue' +')'set @sql = @sql + 'select @inputvalue = + left(@inputvalue, @separator_position - 1)'--set @sql = @sql + 'print @separator_position '--set @sql = @sql + 'print @inputvalue 'set @sql = @sql + 'insert into @tbl(entityid,id,source,reason) values (@inputvalue)'set @sql = @sql + 'end select * from @tbl'print (@sql)exec (@sql)
I think you should use an existing CSV split function to begin with.When you understand what that is doing, you can write your own.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-12 : 05:06:42
|
quote:
Originally posted by madhivanan
Why too much dynamic sql?Make sure you read this fullywww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail
hai, i need to do batch update...means, i want to update only 3 columns in the table...but no of row will be minimum 100..so im planning to get all the input as concatenated string from the UI and then split into rows, insert into @table and from table variable read one by one to update the main table. so i prefer to use @table variab le. otherwise, i need to go for #temp table. i feel using #temp table is more expensive than Dyanmic...that's y i opted for dynamic sql.do u have any better suggesstion for this |
|
|
|
Kristen
Test
22859 Posts |
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-12 : 05:53:14
|
i need to update lot of records..everything will have different where condition to update...meaning, entity id is unique, so where condition will change for every update...that's y reading one by one...and im planning to use save transaction. so that i commit the changes to database after certain number of records updated...i saw the link earlier itself to do this requirement. split function..as i said in the previous reply, my understanding is table variable and dyanmic sql is less expensive than using #temp table..if im wrong pls help me.quote:
Originally posted by Kristen
"insert into @table and from table variable read one by one to update the main table."Why is there a need to do this "one-by-one"?"do u have any better suggesstion for this"Go with Peso's advice on using a split function:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functionsKristen
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-12 : 06:05:13
|
quote:
Originally posted by requestor
if im wrong pls help me.
We have, all morning.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
help me to fix the error