INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Unpivot during Bulk Insert

Unpivot during Bulk Insert

(OP)
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;

CODE

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...

CODE

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.

RE: Unpivot during Bulk Insert

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Unpivot during Bulk Insert

Quote (BxWill)

insert the text files into several Sql Server 2012 table[s]

So I assume you want (normalized tables):

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

and TableB
ID(FK) AcctNo
1234   1002575
1234   1002516
1235   1002446
1235   1002447
1235   1002448
 
You could dump your text file into a Temp table and then read/insert from your Temp table like:

INSERT Into TableA
Select Distinct Product, SalesAmount
From Temp


and then proceed to your other table(s).

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Unpivot during Bulk Insert

(OP)
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.

RE: Unpivot during Bulk Insert

(OP)
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.

RE: Unpivot during Bulk Insert

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:

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 

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

INSERT Into TableA
Select Distinct Product, SalesAmount
From Temp


Would insert Temp table data into TableA, so you would end up with:
TableA
ID     Product                SalesAmount
1      Green Widgets_Part B   $10.00
2      Blue Widgets_Part A    $25.00
 
To populate TableB I would use something like:

Insert Into TableB
Select TableA.ID, Temp.AcctNo
From Temp, TableA
Where Temp. Product = TableA. Product
And Temp. SalesAmount = TableA. SalesAmount


So you should end up with:

TableB
PK_Field  ID     AcctNo
1         1      1002575
2         1      1002516
3         2      1002446
4         2      1002447
5         2      1002448 

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close