Using a cursor to move data into a temp table

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

General SQL Server Forums

New to SQL Server Programming

Using a cursor to move data into a temp table

Author

Topic

k1robert
Starting Member

3 Posts

Posted - 2010-05-27 : 06:56:46

Hi all i'm struggling to do something so I thought i'd come and ask for some help.

I have imported some log files into a table lets call it "MyTable". MyTable has 1 column called "col1"

there are 92159 rows in my table. Each record within the log file that I imported takes up 33 rows.

what I am trying to do is

declare 33 variables of type varchar called @Val1 to @Val33

then i want to go through row by row putting row 1 into @val1, row 2 into @val2 etc

as I have 92159 rows I need to be able to loop through every 33 rows. I have no idea how to do this. Please can someone provide a snippet that i can alter and get started on this. It will be greatly appreciated.

Kevin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 07:35:01

You are not very clear - sorry.
In a table I know rows and columns.
In your post the terms record, row and column are used.
for example I can't understand this: I have 92159 rows I need to be able to loop through every 33 rows

Maybe some sample data would help?

No, you're never too old to Yak'n'Roll if you're too young to die.

k1robert
Starting Member

3 Posts

Posted - 2010-05-27 : 09:16:19

Many thanks for responding....i will attempt to explain myself

Below is a sample of the data (I have altered the text as it is sensitive data but i hope it gives you a feel for the challenge i have to face) that has been loaded from a text file. This is 1 record, within the text file there are about 4000 records. The data that is loaded into the table in col1
Each new line is stored in a row. 33 rows = 1 record

my aim is to get the data firstly row by row (as 33 rows make 1 record), each of the rows should be stored in variables. So lets say i store them in @line1, @line2 to @line33

then i want to insert the whole line into another table. before lastly trying to split the values in each column and put it in a 3rd table that will have proper column headings such as firstname, lastname, nino ect.

[line break character]
BLOGGS
JOE SOME PLACE
111 L60WW 2010
COMPANYNAME 100410

LE 55 90 52 A 00 00 0000 M

London My Company
High Street

London
W1 7RT

RR

Dr J Bloggs
Nataruk
SQL Lane
Highbury, Lewes CR7 3KZ

[line break character]

Kevin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 09:32:37

So the sample record is stored in a table in ONE column called col1?
Or is there a table and each line of the sample record is in it's own col1?

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-27 : 09:45:41

Try re-importing your data using SSMS import wizard and separating your columns during the import.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

k1robert
Starting Member

3 Posts

Posted - 2010-05-27 : 09:58:10

I used the SS Import and Export Wizard, but no matter which column delimiter I try it still puts everything into 1 column

Kevin

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-27 : 10:05:02

if it is every 33 rows, there must be a delimiter. either a fixed length column, or a CR/LF, or a single CR or single LF.

I would try again. Otherwise, it will take a lot of processing (that should not be needed).

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources