How to Split,Loop and insert - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

How to Split,Loop and insert

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-04 : 05:19:17

Hello,

I have a table of Marks with fields

RegNo Sub01 Sub02 Sub03 Sub04

I am gettign dat from my aspx page like(first portion before '/' denots mark and after / gives subjectcode

21/04
34/02
45/01

or

22/03
21/01
32/03

ie, mark and subjectcode can vary.
Now what I want is, I have to store mark to the corresponding field according to SubjectCode
,like if SubjectCode is 04, I have to store corresponding mark to Sub04,if SubjectCode is 01,I have
to store it to Sub01 etc.

Like this I have 16 marks and 16 subject codes...How could I do this

Thanks

In my stored procedure I am getting value like

CREATE PROCEDURE sp_InsertMarks

@RegNo nvarchar(6),
@Mark1 nvarchar(10),
@Mark2 nvarchar(10),
@Mark3 nvarchar(10),
@Mark4 nvarchar(10)
As
Begin

/*Here I have to do Insertion/Updation */

End

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 05:36:48

if your data is fixed length (always 5 chars) then you can use
left(data, 2) to get the mark
and
right(data, 2) to get the subject code

Kristen
Test

22859 Posts

Posted - 2006-06-04 : 06:00:26

Something like this perhaps?


INSERT INTO MyTable(RegNo, Sub01, Sub02, Sub03, Sub04)
SELECT	[RegNo] = @RegNo,
	[Sub01] = CASE WHEN @Mark1 LIKE '%/01' THEN LEFT(@Mark1, 2) ELSE NULL END,
	[Sub02] = CASE WHEN @Mark1 LIKE '%/02' THEN LEFT(@Mark1, 2) ELSE NULL END,
	[Sub03] = CASE WHEN @Mark1 LIKE '%/03' THEN LEFT(@Mark1, 2) ELSE NULL END,
	[Sub04] = CASE WHEN @Mark1 LIKE '%/04' THEN LEFT(@Mark1, 2) ELSE NULL END
WHERE	@Mark1 IS NOT NULL
UNION ALL
SELECT	[RegNo] = @RegNo,
	[Sub01] = CASE WHEN @Mark2 LIKE '%/01' THEN LEFT(@Mark2, 2) ELSE NULL END,
	[Sub02] = CASE WHEN @Mark2 LIKE '%/02' THEN LEFT(@Mark2, 2) ELSE NULL END,
	[Sub03] = CASE WHEN @Mark2 LIKE '%/03' THEN LEFT(@Mark2, 2) ELSE NULL END,
	[Sub04] = CASE WHEN @Mark2 LIKE '%/04' THEN LEFT(@Mark2, 2) ELSE NULL END
WHERE	@Mark2 IS NOT NULL
... repeat for @MARK3 ... @MARK16

You could have a UDF that converted a "@MARKn" into the 4 columns, as a table, and UNION ALL them to get the data to insert. That would probably be easier if the @MARKn data is NOT fixed length format

It would be easier if the data was NOT in separate fields - e.g. if it was in a delimited list. If the data format from the ASP page is for "ease of data entry" then a single TEXTAREA into which the user could type

21/04 34/02 45/01 ...

would be easier to parse into your Marks table.

Kristen

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-04 : 07:08:36

Hello Kristen,

If I can pass the data like

21/04 34/02 45/01 ..., then what will be change in the code? Could you please post it here?

Thanks
Ceema

Kristen
Test

22859 Posts

Posted - 2006-06-04 : 08:13:14

You can SPLIT the delimited list into a temporary table, and then just insert from that.

In fact usually you split the delimited list using a Table UDF, so you can use the UDF instead of a temporary table, thus the process becomes something like:


INSERT INTO MyTable(RegNo, Sub01, Sub02, Sub03, Sub04)
SELECT	[RegNo] = @RegNo,
	[Sub01] = CASE WHEN T.SplitValue LIKE '%/01' THEN LEFT(T.SplitValue, 2) ELSE NULL END,
	[Sub02] = CASE WHEN T.SplitValue LIKE '%/02' THEN LEFT(T.SplitValue, 2) ELSE NULL END,
	[Sub03] = CASE WHEN T.SplitValue LIKE '%/03' THEN LEFT(T.SplitValue, 2) ELSE NULL END,
	[Sub04] = CASE WHEN T.SplitValue LIKE '%/04' THEN LEFT(T.SplitValue, 2) ELSE NULL END
FROM	dbo.MySplitFunction(@MyMarkData) AS T

(Assumes that MySplitFunction returns a single column called [SplitValue] )

For split functions see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting%20delimited%20lists,Best%20split%20functions

Kristen

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-06-04 : 08:22:30

Hello,

Thank you so much, and from this site people like me, who are not so familier with back end queries ,are getting a lot of help.

Thank you once again

Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-05 : 04:15:57

Also read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources