CODEWITHSUNDEEP
sql-serversql-server-2005
chut319
chut319

Reputation: 81

Create table from query result rows

Within SQL Server 2005, I need to create a table dynamically based upon the rows returned from a query.

For example:

SELECT * FROM TableA

returns the following rows

  1. Forename
  2. Surname
  3. DOB

And I'd like a table created like so:

Forename, Surname, DOB.

I plan to use this procedure on a number of different sources therefore the returned rows will be different each time.

Many thanks

Upvotes: 0

Views: 2994

Answers (2)

Derek
Derek

Reputation: 23228

The following query creates a dynamic sql statement that can be executed. You may need to have a separate column in yourtable that has the column's datatype definition as well.

declare @sql nvarchar(max)
select @sql = coalesce(@sql + ', 
    ' + yourcolumn + ' varchar(255)',
    yourcolumn + ' varchar(255)')
from yourtable

select @sql = 'create table tableA(' + @sql + ')'

--exec sp_executesql @sql
select @sql

Upvotes: 0

pkk
pkk

Reputation: 3691

I would use SELECT INTO in conjunction with PIVOT:

See:

I don't have access to SQL Server right now, but I would go for something like this (based on the links above):

SELECT [0] 
INTO TableB
FROM
    ( SELECT id, colName FROM TableA ) AS SourceTable
PIVOT
    ( AVG(id) FOR colName IN [0] ) AS PivotTable;

Upvotes: 1

Related Questions