import textfile into 2 tables in SQL server 2000

[kashif73]

I have a text file with hundreds of records. each line contain 1250 values seperated by a semicolon. I have created 2 tables in SQL server 2000, one with 1000 columns & the second with 250 columns. my question is how can i import this textfile in to these 2 tables.and how i ensure that first 1000 values go to first table & the rest 250 in the next from each line of the text file. can be this be done via VB.NET or ASP? I would be grateful for your help. thanks.

[ck9663]

Try uploading to a single table and split it once it's on the server.

-- CK

[kashif73]

but sql server 2000 does not allow more than 1024 columns in 1 table, isnt it? isthere a way we can put 1250 columns in 1 table???

[ck9663]

That's a lot of columns.

Option 1:

Parse it on front-end and insert to table on the back-end

Option 2:

Upload the string as a single column (nvarchar(max)) of a table, parse it in SQL Server to split into two tables.

-- CK

[kashif73]

How do I parse it in SQL server if i import each line as a single column? Please can you provide a little with code .Many thanks.

[ck9663]

Option 1:

Use a lot of substrings.

Option 2:

Use cursor then tweak this code.

Option 3:

Use CTE


-- CK