How do you create a dynamic temp table?

How do you create a dynamic temp table?

Adding Columns in #Temp table dynamically:

1. DECLARE @ColName nvarchar(100)
2. DECLARE @DynamicSQL nvarchar(250)
3. SET @ColName=’newColumn’
4. SET @DynamicSQL = ‘ALTER TABLE #Mytemp ADD [‘+ CAST(@ColName AS NVARCHAR(100)) +’] NVARCHAR(100) NULL’
5. CREATE TABLE #tmp(ID INT IDENTITY(1,1), Col1 nvarchar(100), Col2 int)

How do you create a temp table from a select statement?

To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#). Now, to see where this table exists; go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”.

How to create temp table with dynamic SQL?

To create a temp table that is filled by a dynamic query, use global temp tables like this example. For the select into statement to work, you need to make sure every column from the select has a name. Do not forget to drop the temp table when your done. Thanks for contributing an answer to Stack Overflow!

Do you have to create a temp table first?

PS 2: Sorry for my poor English level, I tried my best to elaborate it more clearly. without having to create the temp table with fixed columns first. You don’t have to create the temp table or specify the columns first, just select into the temp table and it will be created on the fly.

Can you use null in a dynamic temp table?

You can still use these with the dynamically created temp table. The null above is just to give the 2nd select the same number of columns as the first (I used both selects from your post); this is a requirement for UNION. Thanks for contributing an answer to Stack Overflow!

How to create table for conditional based columns?

I have some conditional based columns like Salary, Code. How can I create a table for conditional based columns? I don’t want to use SELECT INTO #tempTable