March 20, 2018 at 4:34 pm
Hi Friends,
Hope all doing good.
I am currently struck with a requirement at my work.
Requirement:
1. I currently have 5 different CSV files which contains 100 K Phone numbers for each .csv file ( 1-100K .csv file 1; 100k-200k .csv file2; ......400-500K .csv file 5 ) in our destination VM ( Column name is |Phone number| )
2. The SQL Server 2012 DB is hosted on different server ( VM ) which contains many instances and we don't have access.
3. We have to import the 5 csv files ( back to back ), which means parsing the files and get the associated user details for those 500 K phone numbers ( Phone number | user name | location | Status ) from the database which is hosted at different location and export the 5 CSV files with the corresponding user details into the destination VM.
4. For Ex:
Step 1: Importing the .csv files into the Database ( Preferably Temp tables and to be dropped )
Step 2 : Selecting the data based on 500K Phone numbers from Temp tables by joining with Phonedata DB, Phoneuser table ( Phone number, user name , location , status )
Step 3 : Exporting the 500 K Phone users data to the 5 csv files into the destination VM.
Looking for a SQL Script to implement the above 3 steps, so that will make use of the concept and schedule a shell scripted cron job.
Thanks in advance friends for your help friends.
Regards,
Ogirala
March 23, 2018 at 12:58 pm
manoharogirala - Tuesday, March 20, 2018 4:34 PMHi Friends,Hope all doing good.
I am currently struck with a requirement at my work.
Requirement:
1. I currently have 5 different CSV files which contains 100 K Phone numbers for each .csv file ( 1-100K .csv file 1; 100k-200k .csv file2; ......400-500K .csv file 5 ) in our destination VM ( Column name is |Phone number| )
2. The SQL Server 2012 DB is hosted on different server ( VM ) which contains many instances and we don't have access.
3. We have to import the 5 csv files ( back to back ), which means parsing the files and get the associated user details for those 500 K phone numbers ( Phone number | user name | location | Status ) from the database which is hosted at different location and export the 5 CSV files with the corresponding user details into the destination VM.
4. For Ex:
Step 1: Importing the .csv files into the Database ( Preferably Temp tables and to be dropped )
Step 2 : Selecting the data based on 500K Phone numbers from Temp tables by joining with Phonedata DB, Phoneuser table ( Phone number, user name , location , status )
Step 3 : Exporting the 500 K Phone users data to the 5 csv files into the destination VM.Looking for a SQL Script to implement the above 3 steps, so that will make use of the concept and schedule a shell scripted cron job.
Thanks in advance friends for your help friends.
Regards,
Ogirala
You could use BULK INSERT
BULK INSERT SchoolsTempFROM 'C:\CSVData\Schools.csv'WITH( FIRSTROW = 2, FIELDTERMINATOR = ',', --CSV field delimiter ROWTERMINATOR = '\n', --Use to shift the control to next row TABLOCK)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
March 23, 2018 at 3:37 pm
SQL Server Integration Services (SSIS) is another option.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply