I am trying to push data from Excel to SQL Server using bcp command for multiple tables. Currently I have setup my code such as it creates the text files of the data(in a loop) which needs to be pushed and then run a bcp command mentioned below
bcp dbname.schema_name.table_name out C:\table_name.txt -n -S localhost -U username -P password -b 10000
However I would like to do away with the loop and want to run a single bcp command with names of multiple tables as first argument and different text files as second arguments. I tried finding the solution for this online but couldn't come up with anything.
Any help will be much appreciated.
Just a bit of background: the reason I want to do away with the loop and run a single command is, I am observing strange behavior of my code which is when I step through my code it is able to push all the data to SQL Server, However when I run the entire process at once by clicking on the button to which macro is assigned, the code still runs but don't push any data to SQL Server.
To resolve this I tried putting doevents just after the bcp command but all went in vain, then I setup a wait for 5 seconds just after bcp command and it seems to work, but I feel setting up wait is kinda risky business I will never be sure what should be the wait time.
A single BCP command can only push data to a single table. There is no way to pass multiple table names to a single BCP command.