Solved

create table TSQL using..

Posted on 2015-01-12
6
340 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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:
ScottPletcher 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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

20 Experts available now in Live!

Get 1:1 Help Now