更新时间:2023-09-06 19:39:04
基本上,不要 - 这很容易做到,但是无论什么时候你都有一个完全的噩梦做任何改变。 SQL字符串处理非常糟糕,你必须真正搞乱才能做任何事情。
相反,创建一个第二个表,它将一个Class值链接到一个资源ID,并在其中有多个条目。
在实践中,我要做的是有三个表:
资源:Id
...
课程:Id
房间
ClassesInResourceId
ResourceId
ClassId
并填写:
资源:1 John Smith
2 Mary Jones
课程:
1卧室
2厨房
3餐厅
4休息室
ClassesInResource:1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 2 4它可能看起来更复杂 - 它是 - 但它使用起来非常简单,它使后来的编码变得更加容易,更容易!
创建一个用户定义的函数,将逗号分隔值转换为表格。用户定义的函数,它接受逗号分隔的值并返回包含这些值的表。
查看此帖子
sql-server-comma-separated-string-to-table.aspx [ ^ ]
Hi,
In the database , there is a table with three columns, First column is an autogenerated id field, second one is named as 'Resources' and third is 'Classes'. the 'Resources' can have many 'Classes'. For example suppose the 'Resource' field value of one row is 'Room'. The 'Classes' Value in the same row will be bed room, dining room, kitchen..etc.. I would like to insert bed room, dining room, kitchen, in the 'Classes' column with a comma seperation. How can i write an insert query for this?
Basically, don't - it's very easy to do, but it's a total nightmare to work with whenever you have to make any changes. SQL string handling is pretty poor and you have to really mess about in order to do anything.
Instead, create a second table which links a Class value to a Resource ID, and have multiple entries in that.
In practice, what I would do is have three tables:
Resources:Id ...
Classes:Id Room
ClassesInResourceId ResourceId ClassId
And fill them:
Resources:1 John Smith 2 Mary Jones
Classes:1 Bedroom 2 Kitchen 3 Dining room 4 Lounge
ClassesInResource:1 1 1 2 1 2 3 1 3 4 2 1 5 2 3 6 2 4It may seem more complicated - and it is - but it's really very simple to use, and it makes later coding a lot, lot easier!
create a user-defined function that will convert the comma separated value into a table. user-defined function that takes a comma-delimited value and returns a table containing these values.
See this post
sql-server-comma-separated-string-to-table.aspx[^]