Data Import dilemma - 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)

Data Import dilemma

Author

Topic

mkhans
Starting Member

2 Posts

Posted - 2005-06-10 : 19:36:24

Hi All,

I have a table in which two of the columns will have data that looks something like this:

UserA 2(9-12), 3(1-4)
UserB 2(1-47),3(1-47),4(1-47)
UserC 2(1-47),3(1-47)

Here the numbers in paranthesis define a range ...

Essentially what I need to do is for each row insert records in another table so the resultset looks like this:

UserA 2 9
UserA 2 10
UserA 2 11
UserA 2 12
UserA 3 1
UserA 3 2
UserA 3 3
UserA 3 4

and then carry on the same procedure for the remaining rows (UserB etc.)

Any tips/code snippets on how to handle this issue is really appreciated.

TIA.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-11 : 09:41:37

First split up the table to
UserA 2 9 12
UserA 3 1 4
...
This is simple using
http://www.nigelrivett.net/SQLTsql/ParseCSVString.html
to split up the entries then use string functions to populate the 3 columns.
left(s, charindex('(',s)-1), substring(s, charindex('(',s), charindex('-',s) - charindex('(',s) - 1), substring(s, charindex('-',s), charindex('-',s) - charindex(')',s) - 1)

Now you need a tally table - contining all the numbers 1 to the max value populted previously.

It now becomes a simple matter to join the two tables to produce your result.
The work tables should probably be temp tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mkhans
Starting Member

2 Posts

Posted - 2005-06-11 : 11:52:32

Thanks for your feedback. It is possible that the range within the parenthesis will not be contiguous.

UserA 2(9-12,17-25,43-48), 3(1-4)
UserB 2(1-47),3(1-47),4(1-47)
UserC 2(1-47),3(1-47)

So basically I think the steps involved would look somthing like this

UserA 2 9 12
UserA 2 17 25
UserA 2 43 48
UserA 3 1 4

Any suggestions regarding how to pursure further?

Thanks a lot for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources