asked on

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?

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

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.

Jim Horn

>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

Mike Eghtebas

ASKER

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

Jim Horn

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial