0

How can I create a temp table if I don't know the name of columns? Names of columns I get at run time with some dynamic pivot so for example my DECLARE @cols AS NVARCHAR(MAX) now has a comma separated list of columns like '[First_Name],[Last_Name], [Address], [Phone]' . How can I create a temp table now that its columns are coming from that list? ( forget about the data type, everything a NVARCHAR(500) is good enough.

UnskilledCoder
  • 192
  • 1
  • 11
  • 2
    SQL Server doesn't have that kind of construct for `CREATE TABLE` Create it on the fly when you select from your pivot. `SELECT * INTO #tmp FROM...` – squillman Jun 17 '22 at 13:50
  • 1
    @squillman the part I am confused is the #tmp. If I don't know what is #tmp then how do you define it before using it in that SELECT statement – UnskilledCoder Jun 17 '22 at 13:52
  • 1
    In this instance #tmp gets automatically created with column names and datatypes based on the resultset of your query. You don't need to know how to define #tmp, you never issue a `CREATE TABLE` statement. It's created automatically on the fly during the SELECT. #tmp will be equivalent to your resultset (except for PK, indexes, identity, etc) – squillman Jun 17 '22 at 13:53
  • You can dynamically create the `create table` statement at the same time you are building `@cols`. You'll need to create the table directly in `tempdb` as a normal table due to process scoping, taking into account concurrency if relevant. – Stu Jun 17 '22 at 13:55
  • Probably would help us explain it better if you post your dynamic pivot. – squillman Jun 17 '22 at 13:57
  • it doesn't matter. my question is imagine somehow from somewhere I get a comma separated list that are supposed to be column names. Now how do I convert that into a temp table ? – UnskilledCoder Jun 17 '22 at 13:59
  • We're telling you that you don't need to go through the headache of doing that. There is a much simpler way to do it. – squillman Jun 17 '22 at 14:03
  • If you have a string of column names like shown you can just use functions like `replace` and `concat` to add in the relevant data types and `create table` syntax. – Stu Jun 17 '22 at 14:05
  • The problem with creating a temporary table dynamically is that as soon as the dynamic statement's batch ends, the table goes with it. `DECLARE @col sysname = N'MyCol'; DECLARE @SQL nvarchar(MAX) = N'CREATE TABLE #tmp (' + QUOTENAME(@col) + N' int);'; EXEC sys.sp_executesql @SQL; SELECT * FROM #tmp;` will generate the error `Invalid object name '#tmp'.`, as the table has been implicitly `DROP`ed by the time the outer `SELECT` attempts to reference it. – Thom A Jun 17 '22 at 14:15
  • 1
    Honestly, this smells of an [XY Problem](//xyproblem.info). – Thom A Jun 17 '22 at 14:16
  • @Larnu Yes indeed that's so, that's why I mentioned creating a "proper" table in tempDB such that this doesn't happen and you can control exactly if and when it's dropped, taking into account a unique name if concurrency is required etc. – Stu Jun 17 '22 at 14:17
  • There is probably a better way of doing whatever it is you are doing. If you show us the code you have so far we can see – Charlieface Jun 17 '22 at 14:27
  • @Larnu But you can select from *within* the dynamic SQL obviously. `DECLARE @col sysname = N'MyCol'; DECLARE @SQL nvarchar(MAX) = N'CREATE TABLE #tmp (' + QUOTENAME(@col) + N' int); SELECT * FROM #tmp;'; EXEC sys.sp_executesql @SQL;` – Charlieface Jun 17 '22 at 14:28
  • Yes, correct, @Charlieface (I didn't say you couldn't), though I suspect that doing that may be adding additional complexity to a problem the OP is likely already making overly complex. – Thom A Jun 17 '22 at 14:30

0 Answers0