Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

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?
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 3
  • 2
2 Solutions
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now