Unpivot during Bulk Insert 2

[BxWill]

Have several text files that have over 125,000 records. Many of the records have duplicate sales amounts.

Currently creating T-SQL scripts to bulk insert the text files into several Sql Server 2012 table.


For example, the text files are in a format similar to;

AcctNo		Product			SalesAmount
1002575		Green Widgets_Part B	$10.00
1002516		Green Widgets_Part B	$10.00
1002446		Blue Widgets_Part A	$25.00
1002447		Blue Widgets_Part A	$25.00
1002448		Blue Widgets_Part A	$25.00

Initially, I was planning to setup a primary key on the field "AcctNo."


However, although the AcctNo is a unique number, there should be one record per product.

Is it possible to utilize T-SQL bulk insert to reformat the data so that the following is displayed?

If this is not possible, then, maybe the deletion of all records after the first one may be considered...

AcctNo1		AcctNo2		AcctNo3		Product			SalesAmount
1002575		1002516				Green Widgets_Part B	$10.00
1002446		1002447		1002448		Green Widgets_Part A	$25.00

My thought is that this is a sort of un-pivot within T-SQL...

Any insight as to if this is possible and/or feasible?

It is not possible to alter the setup of the text file from the source department whereby the data is normalized - with one
record per product. Have already tried numerous times without much success... Therefore, I have to work with the text files as is.


Thanks in advance.

 

[SkipVought]

Hi,

What happens when there are FOUR AcctNos or more with the same Product?

Your "solution" is, in reality, a horrendous complication for future data analysis/manipulation.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

insert the text files into several Sql Server 2012 table

So I assume you want (normalized tables):

TableA[pre]
ID(PK) Product SalesAmount
1234 Green Widgets_Part B $10.00
1235 Blue Widgets_Part A $25.00[/pre]

and TableB[pre]
ID(FK) AcctNo
1234 1002575
1234 1002516
1235 1002446
1235 1002447
1235 1002448
[/pre]
You could dump your text file into a Temp table and then read/insert from your Temp table like:
[tt]
INSERT Into TableA
Select Distinct Product, SalesAmount
From Temp[/tt]

and then proceed to your other table(s).

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BxWill]

Andrzejek,

Your suggestion appears to be a viable solution.

So, if I understand correctly, your suggestion would result in two tables. Your first table has the three fields - ID (PK), Product, Sales Amount.

(My thought for the three fields is "Acct No", "product" and "sales amount.") However, I did not use an arbitrary id field as a primary key.

And your second table containing just two fields - "ID" and "Acct No."

Great approach!

How are you using T-SQL to arbitrarily assign primary key fields to the same product in both tables?

How would you setup the second table using T-SQL and populate it with all occurrences of the account number with unique IDs by product?

Although I am not familiar with how this is done, it appears that one can use row over partition to accomplish this??

Your example also appears to answer SkipVought's question of what is to be done if I have FOUR AcctNos or more with the same Product?

During data validation using T-SQL bulk insert, I was not able to determine that I have multiple account numbers per product.

Note, I was only able to determine that I had multiple account numbers with the same product after opening the text files in MS Excel during the final stages of the data exploration phase.

Will continue my review.

Appreciate any additional insight.

 

[BxWill]

Revisited this again and it appears that one can use the following to populate Table B

INSERT Into TableB
Select Distinct AcctNo
From Temp


But, I am not sure as to how one can have the ID field in Table B synch with the ID field in Table A whereby for ID in both tables refer to the same product.

Isn't the ID field in Table A incrementally increasing by one? How does one have the ID field in Table B be assigned to the account numbers based on the product in Table A?

In MS Excel, I can see where one can use Index Match to acquire the ID field in Table B based on a third table that would have ID, Product, and Account no.

At this point, it appears that the use of a text editor or MS Excel to split the text file into two files is the best course.

Thanks for the insight.

 

[Andrzejek]

My approach would be:

(Disclaimer: I am sure there are a lot of other approaches that are better than this one)

Populate Temp table with the information from your text file, so your Temp table would look like this:

[pre]Temp
AcctNo Product SalesAmount
1002575 Green Widgets_Part B $10.00
1002516 Green Widgets_Part B $10.00
1002446 Blue Widgets_Part A $25.00
1002447 Blue Widgets_Part A $25.00
1002448 Blue Widgets_Part A $25.00[/pre]

Establish 2 other tables:

TableA
ID (Primary Key field)
Product
SalesAmount

TableB
PK_Field (Primary Key field, every table should have a PK, right?)
ID (Foreign Key to TableA.ID)
AcctNo
[tt]
INSERT Into TableA
Select Distinct Product, SalesAmount
From Temp[/tt]

Would insert Temp table data into TableA, so you would end up with:
[pre]
TableA
ID Product SalesAmount
1 Green Widgets_Part B $10.00
2 Blue Widgets_Part A $25.00
[/pre]
To populate TableB I would use something like:
[tt]
Insert Into TableB
Select TableA.ID, Temp.AcctNo
From Temp, TableA
Where Temp. Product = TableA. Product
And Temp. SalesAmount = TableA. SalesAmount
[/tt]

So you should end up with:

TableB[pre]
PK_Field ID AcctNo
1 1 1002575
2 1 1002516
3 2 1002446
4 2 1002447
5 2 1002448[/pre]

PS. Please do not use names like TableA or TableB, use more meaningful names for your tables.

SQLs not tested.

Have fun.

---- Andy

There is a great need for a sarcasm font.