@id = 1,2,3,4,5 @name = 'NY, NJ, VA, DC,CA' I have a temp table called |
You want to use the insert into #tmp (id,name) select ID.row_value id, VAL.row_value name from sa_split_list(@name) VAL join sa_split_list(@id) ID on ID.line_num=VAL.line_num Thats perfect. Thanks a lot all.
(13 Feb '13, 17:12)
roadtrain64
|
Try something like this: insert into #tmp( id, name ) select a.row_value, b.row_value from sa_split_list( @id ) a join sa_split_list( @name ) b on a.line_num = b.line_num; Note that I have not tested this. You can read more information about sa_split_list in the docs. I noticed that you have some embedded spaces in your comma-delimited strings. If you don't want those included in your tmp table then you will can use trim to remove any that exists. Example: insert into #tmp( id, name ) select trim( a.row_value ), trim( b.row_value ) from sa_split_list( @id ) a join sa_split_list( @name ) b on a.line_num = b.line_num; 1
Looks like Graeme hit enter a few minutes before I did :-) ... I took too long to look up the references in the docs!
(12 Feb '13, 20:40)
Mark Culp
|
CREATE TABLE #tmp_tbl ( qualifier_state_id integer , event_id integer , group_id integer , state_number integer , qualifier_state_name varchar(255) ) INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('SAMLL') INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('YELLO') Thanks but I have qualifier_state_name in my temp table and my qualifier_state_ids are coming from variavle @qualifier_state_ids = '58,61' Where 58= SMALL and 61= Yello Now I need to update below temp table and make qualifier_state_id =58 for small and 61 for Yello I tried below but not workin UPDATE #tmp_tbl a , sa_split_list(@lQualifierStateIds) b SET a.qualifier_state_id = TRIM(b.row_value)-- this updates both state_ids =61 whichh is wrong. Okay Thanks a lot.
(16 Feb '13, 11:14)
roadtrain64
|