Dynamically creating a @table Table from a current table

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  September 27, 2016 at 6:46 pm

  #1903533

  Lynn Pettis (9/27/2016)

  ---

  mister.magoo (9/27/2016)

  ---

  Lee Hopkins (9/27/2016)

  ---

  I have a script that gives me a string of the columns and types.

  I wan to use this result to create a declared table

  DECLARE @listStr VARCHAR(Max)

  SELECT @listStr = COALESCE(@liststr+', ','') +

  COLUMN_NAME + ' ' + Data_type + ' ' +

  CASE WHEN data_type = 'char' THEN '('+ cast(character_maximum_length AS VARCHAR) +')' ELSE '' END + ' ' +

  CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END

  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST'

  SELECT @listStr

  DECLARE @table1 TABLE(@liststr)

  How can i get my @liststr in to the @Table1

  More to the point, why do you want to do this? It seems like you have decided the best way to do something, but don't know how to do it, so maybe it's not the best way.

  Can you describe the actual problem this will help you to solve?

  True, if you want to create a temporary table based on an existing table there is a simple way:

  select * into #temptable

  from dbo.test

  where 1 = 2;

  OR

  select TOP (0) * into #temptable

  from dbo.test

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001