I've got a Windows Form App linked to a Microsoft SQL Server database with loads of tables.
I've got 2 buttons over there (Import and Export) and when clicking on EXPORT, this should trigger a Stored Procedure that will generate a string to export the data only (loads of INSERT statements - not the table definition). The string generated by this will be saved as a script file. This will be later used to IMPORT that data on to a different database.
I was thinking of creating a stored procedure to do this (but I'm not entirely sure how) or if there is a better approach for this, I'm open to any suggestions.
Use Management Studio to generate the script.
Right-click on the database in Object explorer, from context menu choose "tasks", then "generate scripts".
In the dialog, select the tables you want the data scripted out from. On the next page set the destination, and click on the "advanced" button. There find the "type of data to script" option - choose "data only".
 As per the edited question, you can automate this using Microsoft.SqlServer.Management.Smo.Scripter, the same lib the SSMS uses.