Bulk Insert with Calculation(?) Other Options?

Hello - Need some guidance on how best to get one of my data sources into a SQL Server table.

A bulk Insert is nice and easy, but here is where things get complicated.

I need to store a field that is calculated off one of the fields in the source data, but i am not able to store the field the calculation is based off of (not even in a temp table if i can get around it).

Is there a way to do a bulk insert and specify the fields I want, and do the desired calculation before anything is stored in a table? For example, lets say i wanted to store Years of Service (which is calculated off of a snapshot date and a field called Service Date, but I do not want to store the Service date).

Maybe a bulk insert is not the way to go?
Cynthia HillLead ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need to store a field that is calculated off one of the fields in the source data
Please give us some details and a data mockup.  If this is a 'load the data, then run this update with a calculation', then the update can be saved as a Stored Procedure somewhere that gets executed after the bulk insert.

>Is there a way to do a bulk insert and specify the fields I want, and do the desired calculation before anything is stored in a table?
Another possibility is to bulk insert the data into a 'staging table' that is empty before the load, then do whatever calculating you need, then insert that data into the final target table.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>Is there a way to do a bulk insert and specify the fields I want, and do the desired calculation before anything is stored in a table? For example, lets say i wanted to store Years of Service (which is calculated off of a snapshot date and a field called Service Date, but I do not want to store the Service date).

Bulk insert only import the specific data into a table, it will not able to do "extra" works such as calculation. infact, bulk insert could be a way to go in which as also mentioned by Jim above, it may involve other process to produce your final "result" which could be saved into another table. We usually called this whole process as a ETL process.

E = Extraction , in which we import/extract the data from a source...
T = Transformation, in which data are having manipulation in order to produce the outcome
L = Loading, in which the produced result are being stored in the targeted storage with proper format.

Hence, after the bulk insert statement, you may need to have more SQL statement to be run to produce the desired results.

You can read this or search online further for ETL.

Extract, transform, load
https://en.wikipedia.org/wiki/Extract,_transform,_load
0
PortletPaulfreelancerCommented:
I need to store a field that is calculated off one of the fields in the source data, but i am not able to store the field the calculation is based off of (not even in a temp table if i can get around it).
que?

Why on earth can you not store "that field"?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Cynthia HillLead ConsultantAuthor Commented:
Jim - Thanks for your comment. For some of my other data sources, I use a process similar to what you describe. I have stored procedures that do a bulk inserts to tables in a staging area. From there I am able to update or manipulate the data however I need.

However, this data source is different in the fact that it contains a very confidential field I am not able to store for security reasons. However, I need to complete a calculation off of the confidential field and store the results in the database (so store the result of the calculation and not the confidential field itself...and all in a automated fashion of course :)

Paul - That should answer your question as well. The field I need to complete the calculation off of is confidential and I am not allowed to store it in the db. The field names I mentioned previously were only meant as a example.  

Ryan - Thanks for confirming the Bulk Insert will not facilitate the "extra" I am trying to do. I can take that off the list then.

I am going to research OPENROWSET next to see if I can make that work?? I've never used OPENROWSET, so i will just have to play with it.

In MS Access I can do the functionality I want by linking to the file (a CSV file), having a query that completes the calc, then loading into a stating table (for any further manipulations I need to do). Again, the thing that makes this hard is that i cannot store the confidential field in any of the tables in SQL Server, but i need to do a calculation off of it.

Maybe I can research and see if i can link SQL Server to a text file as well(?).

Just wanted to see if anyone had tips on things i could try...or point me in the right direction. Thanks!
0
PortletPaulfreelancerCommented:
>>"I am going to research OPENROWSET next ..."

OPENROWSET(BULK ...) is an approach that allows you to refer to columns in the data file as if it is a table

e.g.
/* examples from
http://bradsruminations.blogspot.com.au/2011/01/so-you-want-to-read-csv-files-huh.html
*/

select TerritoryID
      ,TotalSales
      ,TotalCost
from openrowset(bulk 'C:\CSV Files\Territory Data.CSV'
               ,formatfile='C:\CSV Files\Territory Data.FMT'
               ,firstrow=2) CSVInput

Open in new window

Note you need the FORMATFILE which is an XML file
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="TerritoryID" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="TotalSales" xsi:type="SQLDECIMAL"/>
    <COLUMN SOURCE="3" NAME="TotalCost" xsi:type="SQLDECIMAL"/>
  </ROW>
</BCPFORMAT>

Open in new window

Ref: XML Format Files (SQL Server)
https://msdn.microsoft.com/en-us/library/ms187833.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cynthia HillLead ConsultantAuthor Commented:
Thanks All for sharing your expertise!

I was able to get a linked server to work pointing to a CSV file (format the source data is in). So i was able to access the data that way, select the fields I wanted, complete the calculation I needed...without having to store the filed I was trying to avoid storing in the DB (oh happy day).

Paul - Thanks for the sample code for the openrowset option, I have that working as well, but read somewhere that is meant to be more for occasional data loads(?). So, I went with the linked server. One thing to note is that I did not need the Format file you mentioned. Which is good, b/c it made things easier.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.