I'd like to write a script that allows me to export .csv files from 15-20 temporary tables I created, using a script instead of having to copy and paste in a separate .csv file and then save them down.
:!!sqlcmd -S server -d database-E -Q "SET NOCOUNT ON
SELECT * FROM TABLE" -o "C:\Users\name\Documents\folder\filename.csv"
-W -w 1024 -s ","
I've tried this, which works (not formatting correctly) but it doesn't seem to be work at all for a temp table; the .csv file contains this.
Msg 208 Level 16 State 1 Server SERVERNAME
Invalid object name '#TEMPTABLE'.
I cannot obtain "elevated privileges" to be able to use BCP export, because I cannot write a stored procedure, create a new database, or access the command line. Is there a workaround for this?
Temp tables are ephemeral; they do not persist across sessions. Instead of creating temp tables, create actual tables, either in the database that you're working with, or in tempdb, then export the data from tempdb
An example:
sqlcmd -S server -d database -E -Q "If Exists (select * FROM tempdb.sys.tables WHERE name = 'Tmp_DataExport1') drop TABLE tempdb..Tmp_DataExport1;"
sqlcmd -S server -d database -E -Q "SELECT TOP 5 * INTO tempdb..Tmp_DataExport1 FROM T_SourceTable"
sqlcmd -S server -d database -E -Q "SELECT * FROM tempdb..Tmp_DataExport1" -o "c:\temp\filename.csv" -W -w 1024 -s ","