Dynamic Table Creation in SQL Server 2008 using stored procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server

how to create dynamic table creation in sql server 2008 using stored procedure

Posted 4-Oct-12 19:45pm

vidya jobin

Updated 19-Apr-17 3:21am

Add a Solution

3 solutions

Solution 3

Hi,

Tables can be created dynamically in following ways.

1. Dynamic SQL -- 'Need to construct query to create table'.
Note :- here you have to check whether the table is already there
or not before executing the Dynamic sql query.

2. SELECT statement

Here we have two options to create table.

a)Create table along with data

SQL

SELECT A.COL1,A.COL2... INTO NEWTABLE
         FROM EXISTINGTABLE

If you execute this query ... sql server not only creates a table
according to the SELECT criteria but also inserts the result set
of the query into the newly created table.

b)Create table with schema only

SQL

SELECT A.COL1,A.COL2... INTO NEWTABLE
FROM EXISTINGTABLE WHERE 1=0

This query creates table according to the SELECT criteria. Here
data will not be stored in newly created table.

Thank you

Posted 10-Oct-12 21:16pm

damodara naidu betha

Updated 10-Oct-12 21:22pm

v3

Solution 1

U should try like that :

SQL

ALTER PROCEDURE spName(
DECLARE @tableName VARCHAR(100),@columnName VARCHAR(50),@Query VARCHAR(8000) )
BEGIN
SET @Query='CREATE TABLE '  + @tableName + '(' +@columnName + ' VARCHAR(200)'+ ')'
EXECUTE (@Query)
END

Posted 4-Oct-12 20:13pm

solanki.net

Comments

brahmaiah77 7-Oct-15 2:35am

Hi ,
this is shiva,
i have already dynamic table , how can i insert into data to table using SP. please send link this mail id:- shivaiah.dantala@gmail.com

Member 4147938 6-Apr-16 9:24am

hi myself anuj saxena Below code is good for dynamic table creation but i will provide totally dynamic code as soon as possible.....

Richard MacCutchan 6-Apr-16 10:43am

This question is over 3 years old. Please do not post in dead questions.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-10-04T20:39:00

How about something like:

SQL

CREATE PROCEDURE sproc_CreateTableAtRuntime
           @TableName NVARCHAR(128)
          ,@Column1Name NVARCHAR(32)
          ,@Column1DataType NVARCHAR(32)
          ,@Column1Nullable NVARCHAR(32)

AS

DECLARE @SQLString NVARCHAR(MAX)
SET @SQString = 'CREATE TABLE ' + @TableName + '( '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable +') ON PRIMARY '

EXEC (@SQLString)

To execute:

SQL

EXEC sproc_BuildTable 'Customers','CustomerName','VARCHAR(32)','NOT NULL'

If need, similar discussion here.[^]

Also, refer: How to dynamically create a table using SQLServer stored procedure [^]