How can I separate a one-column table into a table with multiple columns?

I came across with a text file that I need to import to SQL Server 2005. The data looks like this:

A1A00001
A2Name
A3Address
A4Credit
A5ModeOfPayment
D1Invoice 1 Amount
D1Invoice 2 Amount
D1Invoice N Amount (number of invoice varies per entry)
D2Total Amount   Amount
S1Total Outstanding Amount
S1
A1A00002
A2Name
A3Address
A4Credit
A5ModeOfPayment
D1Invoice 1 Amount
D1Invoice 2 Amount
D1Invoice N Amount开发者_StackOverflow社区 (number of invoice varies per entry)
D2Total Amount   Amount
S1Total Outstanding Amount
S1
A1A00003
A2Name
A3Address
A4Credit
A5ModeOfPayment
D1Invoice 1 Amount
D1Invoice 2 Amount
D1Invoice N Amount (number of invoice varies per entry)
D2Total Amount   Amount
S1Total Outstanding Amount
S1

As you can see, there are no delimiters in the data, that's why I only managed to have a table with one column containing the information above.

I need your help on how to capture one entry from A1 to S1, put it into separate columns, then proceed to the next occurrence of A1 and S1 and so on.

Like A1A00001 A2Name A3Address A4Credit A5ModeOfPayment D1Invoice 1 Amount D1Invoice.. etc

Thanks in advance!

This is a classic example of a file I would send back to the provider and tell them to send it in an appropriate format.

Unfortunately that isn't always an option. In the past how I have handled such is to add a recordidentifer column to the one column table and then populate it to keep all the records together that should be together. I also would add an Identity column at the time the records are inserted so youhave something to order on.

Then you populate the record identifier probably using a cursor or loop to add the same record identifier for each group of related records.

Now create the normalized staging tables you actually need. Populate them with SQl code (which is possible now that you have a recordidentifier). Then popluate your real tables form these nomalized staging tables.

If you have to do this in sql then use cursor. But best would be to parse and insert data to database with some application.

With cursor it would look like this (pseudocode):

If A1 insert previous row if exist

Start prepering next row

If this was like an initial load and you expect to keep the data and maybe add/modify it I would split it into two tables. Without seeing actual data something like this might work. Obviously I just made up data types and column lengths, adjust as necessary.

Group A1 through A5, D2 and S1 into one table. Actually D2Total Amount could probably not be stored in a table but derived from invoice amounts. I included it in the table just for the example.

CREATE TABLE baseInfo (
    A1 INT NOT NULL,
    Name VARCHAR(25) NOT NULL
    Address VARCHAR(55),
    Credit VARCHAR(12),
    ModeOfPayment VARCHAR(12)
    TotalAmount MONEY,
    OutstandingAmount MONEY
    CONSTRAINT [PK_basinfoA1] PRIMARY KEY CLUSTERED (A1)
)

For D1, the invoice amounts, just a two column table. A1 to relate back to the account ID in baseInfo and the amount of the invoice.

CREATE TABLE invoice (
    A1 INT NOT NULL,
    invoiceAmount MONEY
)
ALTER TABLE invoice WITH CHECK ADD CONSTRAINT FK_invoice_base FOREIGN KEY (A1) REFERENCES baseInfo (A1)

To get the data into the two tables you could use TSQL but personally I’d go back to the original text file and use Powershell to parse the text and build SQL inserts.