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-05 : 19:41:23
|
| declare @filter varchar(100)set @filter = '10,''firststring''||10,''secondstring'''declare @tbl table(id decimal,name varchar(20))insert into @tbl values (substring(@filter,0,patindex('%||%',@filter)))hai in the above exmaple, i recieve input value (@filter) as concated string . pipeline(||) is my delimiter..i want to split the string based on this delimater and need to insert into @tbl..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.What is the error in this. i believe i can do this way to insert to concatinated values.Help pls |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-05 : 20:09:54
|
You need Dynamic SQL to do that, but there are other methods that are better I'm sure. Here is a way to do it with Dynamic SQL:declare @filter varchar(100)declare @sql varchar(1000)set @filter = '10,''firststring''||10,''secondstring'''select @sql = 'declare @tbl table(id decimal,name varchar(20)) '+ 'INSERT INTO @tbl(id,name) SELECT '+ substring(@filter,0,patindex('%||%',@filter))exec (@sql)
Future guru in the making. |
 |
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-05 : 20:25:54
|
It work..But i dont understand y dont i achieve the same without using dynamic SQL? do u have any answer for this....Thanks quote:
Originally posted by Zoroaster
You need Dynamic SQL to do that, but there are other methods that are better I'm sure. Here is a way to do it with Dynamic SQL:declare @filter varchar(100)declare @sql varchar(1000)set @filter = '10,''firststring''||10,''secondstring'''select @sql = 'declare @tbl table(id decimal,name varchar(20)) '+ 'INSERT INTO @tbl(id,name) SELECT '+ substring(@filter,0,patindex('%||%',@filter))exec (@sql)
Future guru in the making.
|
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-05 : 21:41:05
|
I believe the reason is that without using Dynamic SQL the query engine reads the variable value as a string value which it attempts to insert as one value when it needs two.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 04:06:08
|
Are you trying to insert TWO rows into the table?id name== ============10 firststring10 secondstring Kristen |
|
|
|
requestor
Starting Member
17 Posts |
Posted - 2007-10-06 : 10:18:04
|
Yes in my requirement i need to insert more than one row. actully after that i need to some other logic, like insert the data into actual data. so i dont want to use dynamic sql...Ok...let me explain my requirement. i have UI, from which user can enter some text information ...that im trying to insert into the table..but requirement is, for on id there should be only one row should be generated. if second user try to insert value in to the same, id, i shouldnot insert it and i should show the error. obviously we have primary in my table. But in single button click i will get value for more that one rows in concatenated string format. The format of the string i have already specified in my previous mail. so im planning to store all the input rows into Table variable. then check if that row already exists in the main table. if it exists i need to flag that row and show the error to the user...All i need to do in single database call..pls help me..thanks in advance...quote:
Originally posted by Kristen
Are you trying to insert TWO rows into the table?id name== ============10 firststring10 secondstring Kristen
|
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
How to do separate the concatenated string