Solved

create table TSQL using..

Posted on 2015-01-12
6
472 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
  • 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 33

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now