Insert comma seperated values into multiple column

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert comma seperated values into multiple column

Author

Topic

maruthi_p
Starting Member

8 Posts

Posted - 2010-03-22 : 03:14:30

Hi,
I've requirement where user can enter multiple columns like first name, lastname, email, age, sex fields and insert them into database.
on my frontend aspx page, i'll be showing a single row initially with 5 text boxes(first name, lastname, email, age, sex) and a button to add more columns. when user clicks on add more, another 5 textboxes will be shown and then i'll be taking 5 + 5 (textbox values) and comma seperate them and need to insert into a table with primary auto increment key ID and the remaining 5 columns(firstname, lastname, email, age, sex).
I previously worked on the same requirmeent but with only single column.
Now i need to insert for 5 columns, How can i do this?
Below is my stored proc which i used for single column insertion.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_add_hotel_amenities_1] 

     @hotel_amenities_id_list varchar(4000),  
     @hotel_id varchar(50)  

   AS
   BEGIN
   SET NOCOUNT ON;

         DECLARE @pos int, @curruntLocation char(20)  
         SELECT @pos=0  
         SELECT @hotel_amenities_id_list = @hotel_amenities_id_list + ','  
		 SET @Pos = CHARINDEX(',', @hotel_amenities_id_list)

         WHILE @pos > 0  
         BEGIN  
			SELECT @curruntLocation = RTRIM(SUBSTRING(@hotel_amenities_id_list,1,@pos-1))  

			INSERT INTO ref_hotel_amenities (hotel_amenity_id, hotel_id) VALUES (CONVERT(bigint,@curruntLocation), @hotel_id)  
			
			SET @hotel_amenities_id_list = RIGHT(@hotel_amenities_id_list, LEN(@hotel_amenities_id_list) - @Pos)
			SET @Pos = CHARINDEX(',', @hotel_amenities_id_list, 1)
		END

		-- LIST THE RESULTS
		SELECT @hotel_amenities_id_list=SUBSTRING(@hotel_amenities_id_list,@pos+1,4000)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 03:20:50

use the fnParseString to extract individual element.

KH
[spoiler]Time is always against us[/spoiler]

maruthi_p
Starting Member

8 Posts

Posted - 2010-03-22 : 05:23:00

quote:
Originally posted by khtan

use the fnParseString to extract individual element.

KH
[spoiler]Time is always against us[/spoiler]

Hi,
I didnt understood that function, can you please let me know is there any simple method to insert multiple rows under multiple columns in table?
My frontend data will be coming like this,
frstname1, lastname1, email1, age1, sex1, firstname2, lastname2, email2, age2, sex2 ....
These data needs to be inserted under my table columns [firstname, lastname, email, age, sex]
Please help me out..
Many Thanks..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 05:40:14

try this example


declare @var varchar(200)

select 	@var = 'a.ab.abc.abcd.abcde'

select 	@var,
	dbo.fnParseString(-1, '.', @var), 
	dbo.fnParseString(-2, '.', @var),
	dbo.fnParseString(-3, '.', @var),
	dbo.fnParseString(1, '.', @var),
	dbo.fnParseString(2, '.', @var)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 11:42:19

quote:
Originally posted by maruthi_p

quote:
Originally posted by khtan

use the fnParseString to extract individual element.

KH
[spoiler]Time is always against us[/spoiler]

Hi,
I didnt understood that function, can you please let me know is there any simple method to insert multiple rows under multiple columns in table?
My frontend data will be coming like this,
frstname1, lastname1, email1, age1, sex1, firstname2, lastname2, email2, age2, sex2 ....
These data needs to be inserted under my table columns [firstname, lastname, email, age, sex]
Please help me out..
Many Thanks..

will your number of fields be fixed always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maruthi_p
Starting Member

8 Posts

Posted - 2010-03-22 : 14:07:39

quote:
Originally posted by visakh16

quote:
Originally posted by maruthi_p

quote:
Originally posted by khtan

use the fnParseString to extract individual element.

KH
[spoiler]Time is always against us[/spoiler]

Hi,
I didnt understood that function, can you please let me know is there any simple method to insert multiple rows under multiple columns in table?
My frontend data will be coming like this,
frstname1, lastname1, email1, age1, sex1, firstname2, lastname2, email2, age2, sex2 ....
These data needs to be inserted under my table columns [firstname, lastname, email, age, sex]
Please help me out..
Many Thanks..

will your number of fields be fixed always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi,
The number of fields are fixed in my table.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 10:37:44

if yes, you can pass them as csv and use suggestion of Tan. Otherwise you could try passing them as xml nodes and then shred them and populate table with values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

donaldjeo
Starting Member

1 Post

Posted - 2010-04-06 : 03:27:24

Hmmm... its really nice that you understand all process to store the data and on click next 5 more text box appear. i want to say thanks to vishakh16. who give best solution for maruthi_p...

Post by Donald

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 05:14:14

quote:
Originally posted by donaldjeo

Hmmm... its really nice that you understand all process to store the data and on click next 5 more text box appear. i want to say thanks to vishakh16. who give best solution for maruthi_p...

Post by Donald

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources