How to create dynamic table using two base tables

Question

0.00/5 (No votes)

See more:

Hi,
I have created two tables in my sql server DB with following Schema:
Table1: SchemaModel

SQL

Create table SchemaModel(
      SchemaID int identity(1,1) primary key,
      Name varchar(20),
      Format varchar(10),
      SchemaCode varchar(20),
      TableName varchar(20)
      Status int
)

Table2:SchemaFields

SQL

Create table SchemaFields(
   SchemaID int                   --same values of the above
   FieldName varchar(30)
   Sequence varchar(20)
   FieldId int identity(1,1) primary key   
)

now we have this two tables in our DB
i need to create table with name "TableName" and contains the columns as 'FieldNames' of the SchemaFields table.
how to create table ......please answer me
i am waiting for u'r response...

Posted 22-Dec-15 20:53pm

Raviteja Swayampu

Updated 22-Dec-15 21:19pm

Tomas Takac

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 23-Dec-15 3:08am

You should learn about VIEWs in SQL...

Naveen.Sanagasetti 23-Dec-15 3:27am

agree with you..

1 solution

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)

CHill60 · Answer 1 · 2015-12-22T22:40:00

If this is a "one-off" - i.e. you are only going to do it once and it's not going to be in a stored procedure that gets called time and time again, then you could use a temporary table[^]
E.g.

SELECT [TableName], SF.*
INTO #dynamicTable
FROM SchemaModel SM
INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID

which you can query just like any other table

select * from #dynamicTable

If you are going to use temporary tables though you must be aware of their scope[^] - i.e. when they will and will not be available.

BUT - If you are likely to look at this data often then create a View[^] of the data e.g.

CREATE VIEW dynamicTable AS
	SELECT [TableName], SF.*
	FROM SchemaModel SM
	INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID

Although the view doesn't physically exist you can query it as you would any other table

select * from dynamicTable

including being able to join to it

SQL

select * from dynamicTable DT
INNER JOIN SchemaFields SF on DT.SchemaID = SF.SchemaID