Hi all,
I was looking to insert multiple values to a single column at a time in SQl.
Any help pleas!
Thank you
Ephi:
What exactly do you mean by "multiple values"? Different data types? A vector? An array? What exactly.
Dave
|||
Lets say I have a single Column called X and I want to insert multiple values into X like (1,2,3) at a time through using the insert statement.
Thank you in advance.
|||Hi,
In reality it is not acceptable according to data normalization rules.
I.e. if you want to have such behavior you should better create an additional table and have foreign constraints mapping to it.
Some example:
table_x(
field_1 .....,
field_2 .....,
field_in_which_you_want_to_have_multiple_values.....
);
table_y(
value_identifier .....,
value nvarchar(1024) ....
);
So you firstly insert several values to table 'table_y' and then just add
value_identifier to a 'table_x.field_in_which_you_want_to_have_multiple_values'|||
do you mean you want to create multiple rows, with one row for each value, using a single Insert statement?
This is not possible. Insert only creates one row in the table.
Unless, of course, you are inserting into one table using the values from another table, in which case you can use the insert...select syntax.
Normal insert syntax:
insert table_name
(col1, col2, col3)
values
(val1, val2, val3)
only inserts one row.
|||I would recommend you use Itzik Ben-Gan's Split function:
CREATE FUNCTION dbo.fn_SplitTSQL
(@.arr NVARCHAR(MAX), @.separator NVARCHAR(1) = N',') RETURNS TABLE
AS
RETURN
SELECT
n - LEN(REPLACE(LEFT(@.arr, n), @.separator, '')) + 1 AS pos,
SUBSTRING(@.arr, n,
CHARINDEX(@.separator, @.arr + @.separator, n) - n) AS element
FROM dbo.Nums
WHERE n <= LEN(@.arr)
AND SUBSTRING(@.separator + @.arr, n, 1) = @.separator;Once you've got this, you could do something like:
insert into mytable (col1, col2, col3)
select 'Val1', 'Val2', element
from dbo.fn_SplitTSQL(N'1,2,3',N',')
This should handle it nicely for you. Oh yes, and you'll need a table called Nums with a field called 'n', which you have populated from 1 to some arbitrarily large number. 1000 might be big enough for most of your uses...
There's more on this at:
http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt
Robsql
