SQL Server Performance Forum – Threads Archive

How to use functions in BCP

Hi, How do we use User-Defined Functions or System functions while bulk inserting data from txt file into the destination table through bulk insert (BCP). Scenario: 1) Extract Name , Sex , Age from ‘Patient#%92 table in DB SouthIndia using BCP into a txt file “Patient.txt” Name Sex Age Thiyagu 001 27 Venket 001 28 Simran 002 26 SexMaster Table Code Name 001 Male 002 Female 003 Unknown. 2) Now the data from the text file should be inserted into table called ‘Client#%92 in different database (MyPubs). The Gender table in mypubs DB (equivalent to SexMaster in SouthIndia DB) is of the below structure, it contains the mapping data to SEX table in SouthIndia DB Gender Table Code Name MappingCode M Male 001 F Female 002 U Unknown 003 Note: The column MappingCode in gender table contains the mapping information to SexMaster table in SouthIndia DB. 3) A function DBO.GetGenderCode() is written in MyPubs DB to retrieve the Gender code from Gender Table for supplied code(SexMaster.Code). The Mapping Sex code from Gender table is found by searching the column MappingCode. 4) Now the Data must be Bulk inserted into ‘Client#%92 table in MyPubs DB from the extracted file “Patient.txt”. But the Sex code (001 / 002 / etc) must be replaced with their equivalent gender code using the function DBO.GetGenderCode(). Question: How do we call or use the function DBO.GetGenderCode() in copying the data to the destination table (client) from file “Patient.txt” Thanks Thiyagu

---

I think you can use FORMATFILE option in BCP for such activity.
And if not only after you upload to a table you can apply the constraint of checking DOB is null and replace with appropriate value. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

I have re-phrased the question. Please refer to my new query.

---

]]>