Is there a quick way to insert a hardcoded csv/txt table into SQL when I don't have permission to write or create a temp table?
I checked that select 1, 2, 3 actually creates an 1,2,3 table with no column names.
Is it possible to copy/paste my csv in ADS/SSMS as TAB/comma/semicolon delimited and convert it into a table with the first row as column names and the format automatically detected if possible or just text if not?
After that, I would like to be able to do so from a locally stored file (in case the csv is too large and cannot paste), is there anything for that?
I got some error when I tried to create a table but it could be due to trying to write it (don't remember).
But how do I insert a csv as a SQL temp table?
It is an one time manual thing but could be continuous too.
I was thinking something like: insert c:\file.csv into Table1 -- to be stored as temp table so that I can e.g. use it as a CPE to filter a table in the db
If you are blocked from creating temp tables - then you are probably also blocked from using any of the available tools to read the files.
BULK INSERT
OPENROWSET
Since temp tables are scoped to the current connection - the table wouldn't be available to other sessions, unless you used a global temp table and made sure the connection that created the global temp table remains available until you are done.
What brand of relational database are you using? I ask because your code flat out won't work in SQL Server even if you has sysadmin privs.
And, just because you got an error, it doesn't mean that you don't have privs to do something. Please stop making stuff up. Still with the facts and not your presumptions.
And, to be sure, you didn't even try to use a Temp Table. Where did your insistence about not being able to use a Temp Table come from. The answer is presumptions based on a lack of knowledge. You'll do much better on forums and in real life if you stop doing that.
There are a few ways to load data from a CSV or TSV file into a table (temp or permanent). One is to use BULK INSERT. Another is to use a file-based linked server. A 3rd is to use BCP from the command line. And there are more. For example, someone mentioned the use of OPENROWSET. That's a 4th method but it also means that the DBA is going to need to enable "ad hoc distributed queries" and, because some DBAs are misinformed, won't do that because they think it's a security risk... and done incorrectly, it can be.
You should ask your DBA to print a list of your server level and database level privs so you can read about the various methods to know two things... what privs you actually have and what privs you may actually need to have.
Probably the most benign T-SQL method is to used BULK INSERT.
According to the documentation to use OPENROWSET with the BULK option you need ADMINISTER BULK OPERATIONS or ADMINISTER DATABASE BULK OPERATIONS.
For BULK INSERT - you need INSERT and ADMINISTER BULK OPERATIONS and possibly ALTER TABLE permissions.
I believe the ad-hoc distributed queries comes into the mix if you use a data source to read the file instead of using the BULK option. Now, there are other permission requirements depending on how you login to SQL Server and where the file is located and whether you are sysadmin or non-sysadmin - but that is a different issue.
Mmm thanks but I assume a SQL/Azure db will have a way to ingest data from various files/sources? I have not used SQL in anything apart from selections but I need to be able to add data and do some ETL things.
Mmm can we access SQL servers via Powershell? That would be great! I remember it is extremely fast but not sure if this will materialise in a SQL query.