create table TSQL using..

Select COLUMN_NAME, Data_Type From tblMeta gives me something like:
       
COLUMN_NAME   Data_Type      CHARACTER_MAX_LENGTH
---------------------      ---------------       -------------------------------------
FirstName              varchar                            25
LastName              varchar                            25
Address                      varchar                            50

How can I make a table in a Proc with table name passed as its parameter to create a new table base on the above data?
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I don't see any issue, looks pretty straightforward to me.  If you don't have an ID column / sequence_number in tblTestMeta, you should add one, so that the columns come out in the specific order you want.


CREATE PROCEDURE spMakeTable
    @table_name nvarchar(350), --[database_name.][schema_name.]table_name
    @metadata_table nvarchar(350), --[database_name.][schema_name.]table_name
    @display_sql bit = 0,
    @exec_sql bit = 1
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)

SET @sql = 'SELECT @sql = (
    SELECT '', ['' + FieldName + ''] '' + FieldProperty + '' NULL''
    FROM ' +
    ISNULL('[' + PARSENAME(@metadata_table, 3) + '].', '') +
    ISNULL(PARSENAME(@metadata_table, 2) + '.', 'dbo.') +
    PARSENAME(@metadata_table, 1) +
    '--ORDER BY ID
    FOR XML PATH('''')
    )
'
EXEC sp_executesql @sql, N'@sql nvarchar(max) OUTPUT', @sql2 OUTPUT
SET @sql2 = STUFF(@sql2, 1, 1, '')
SET @sql = 'CREATE TABLE ' +
    ISNULL('[' + PARSENAME(@table_name, 3) + '].', '') +
    ISNULL(PARSENAME(@table_name, 2) + '.', 'dbo.') +
    PARSENAME(@table_name, 1) +
    '( ' +
    @sql2 +
    ' )'
IF @display_sql = 1
OR @exec_sql = 0
    SELECT @sql AS sql_to_run
IF @exec_sql > 0
    EXEC(@sql)
GO
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
CREATE PROC foo (@table_name varchar(100)) AS

/*
Looky here, meaningful code comments
2014-01-12  eghtebas  Completely original work
*/

Declare @sql varchar(1000) 

-- If the table exists, drop it
SELECT @sql = 'IF EXISTS(SELECT name FROM sys.tables WHERE name=''' + @table_name + ''')'
SELECT @sql = @sql + ' DROP TABLE ' + @table_name
exec sp_executesql @sql

SELECT @sql =  'SELECT COLUMN_NAME, Data_Type INTO ' + @table_name + ' FROM tblMeta' 

-- TESTING ONLY, at first uncomment the below line, run the code, copy-paste into SSMS, and verify it works. 
-- SELECT @sql

-- PRODUCTION ONLY
exec sp_executesql @sql
GO

Open in new window


< edited after original post >
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Jim,
The Proc below runs okay. The sample date in table tblTestMeta is shown below. I need code to create tblTemp with the listed field names in tblTestMeta:
IF OBJECTPROPERTY(object_id('dbo.spMakeTable'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].spMakeTable
Go
CREATE PROC spMakeTable (
@table_name varchar(100)
, @table_nameMeta varchar(100)
)
 AS

Begin
Declare @sql varchar(1000);

-- If the table exists, drop it
SELECT @sql = 'IF EXISTS(SELECT name FROM sys.tables WHERE name=''' + @table_name + ''')'
--SELECT @sql = @sql + ' DROP TABLE ' + @table_name
--exec sp_executesql @sql
Select 'so far so good'
End
go
exec spMakeTable 'tblTest', 'tblTestMeta'

Open in new window


tblTestMeta:
FieldName       FieldProperty
CustomerID	int
FirstName	varchar(30)
LastName	varchar(30)
Address	        varchar(50)
City	        varchar(30)
State	        varchar(2)
ZipCode  	varchar(10)
Phone	        varchar(12)
Email	        varchar(100)

Open in new window


At a later point, tblTestMeta will be created as temp table inside the proce whic will be discussed in a new post later on.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The sample date in table tblTestMeta is shown below. I need code to create tblTemp with the listed field names in tblTestMeta:
So if your requirement is 'make a copy of tblTestMeta as tblTemp with all columns and data, assuming no Identity columns,  then just use * instead of defining the columns, and switch the table names around

SELECT @sql =  'SELECT * INTO tblMeta FROM ' + @table_name

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I must have done terrible job in describing what I want. I have tblTestMeta based on which I want to create tblTest:

tblTestMeta
FieldName       FieldProperty
CustomerID	int
FirstName	varchar(30)
LastName	varchar(30)
Address	        varchar(50)
.
.
.

Open in new window


tblTest (showing column captions only, not populated yet.)
CustomerID   	FirstName	LastName	Address	        ...

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So, you need to create table tblTemp, that contains the column names of tblTestMeta?
One column for all names, or with the same column name / data type?

In other words, give me the schema of tblTest.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.