Solved

create table TSQL using..

Posted on 2015-01-12
6
477 Views
Last Modified: 2015-01-28
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?
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40545887
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40546061
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40546551
>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40546838
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40546851
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 40547490
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question