Create a table from a table of data definitions

Question

Post reply

Create a table from a table of data definitions

matt020876

SSC Rookie

Points: 33
More actions
February 4, 2010 at 8:42 am

#141084

Hi i have a table that has teh following info;
TABLENAME,FIELDNAME,DATATYPE, Value
tbltest field1 varchar(10) testvalue1
tbltest field2 varchar(10) testvalue2
I need a stored procedure to create a table (temp or query) and then populate it with the values = ive tried pivot to no avail - any ideas?
gthanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Try this. Also, see the first link in my signature.

-- first, make a table and populate it with some sample data.

-- when you do this, all of the volunteers on this site can then just

-- cut-and-paste it into SSMS to start working.

DECLARE @Table TABLE (TABLENAME varchar(50),

FIELDNAME varchar(50),

DATATYPE varchar(50),

Value varchar(50))

INSERT INTO @Table

select 'tbltest', 'field1', 'varchar(10)', 'testvalue1' UNION ALL

select 'tbltest', 'field2', 'varchar(10)', 'testvalue2'

--define and populate some variables

declare @TableName varchar(50), @SqlCMD varchar(1000)

set @TableName = 'tbltest'

-- make the create table statement

select @SqlCMD = 'CREATE TABLE #' + @TableName + ' (' + STUFF((select ', ' + FieldName + ' ' + DataType from @Table where TABLENAME = @TableName FOR XML PATH('')),1,2,'') + ' )'

-- make the insert statement

select @SqlCMD = @SqlCMD + ';INSERT INTO #' + @TableName + ' SELECT ' + STUFF((select ',' + QuoteName(Value, char(39)) from @Table where TABLENAME = @TableName FOR XML PATH('')),1,1,'')

-- show the results

select @SqlCMD = @SqlCMD + ';select * from #' + @TableName

-- run everything

execute (@SqlCmd)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

matt020876 SSC Rookie Points: 33 More actions · Answer 2

This works well if there is only 1 value per field - but the table has multiple values per field

An example of tghe data I have is below.

Report1field1test1

Report1field2test2

Report1field2test3

Report1field2test4