如何使用两个基表创建动态表 [英] How to create dynamic table using two base tables

问题描述

我在我的sql server DB中创建了两个表，其中包含以下Schema：

表1：SchemaModel

 创建 表 SchemaModel（
 SchemaID  int   identity （ 1 ， 1 ） primary   key ，
 Name  varchar （ 20 ），
格式 varchar （ 10 ），
 SchemaCode  varchar （ 20 ），
 TableName  varchar （ 20 ）
状态 int  
）

表2：SchemaFields

 创建 表 SchemaFields（
 SchemaID  int    -   以上相同的值 
 FieldName  varchar （ 30 ）
 序列 < span class =code-keyword> varchar （ 20 ）
 FieldId  int  身份（ 1 ， 1 ）< span class =code-keyword> primary   key  
）

now我们在DB中有两个表

i需要创建名为TableName的表，并包含SchemaFields表的'FieldNames'列。

如何创建表......请回答我

i等待你的反应......

解决方案

如果这是一次性 - 即你只打算做一次并且它不会出现在一次又一次调用的存储过程中，那么你可以使用临时表 [ ^ ]

例如

 SELECT [TableName]，SF * 
分成#dynamicTable 
 FROM SchemaModel SM 
 INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID 

其可以查询就像任何其他表

 select * from #dynamicTable 

如果你打算使用临时表，你必须知道他们的范围 [ ^ ] - 即什么时候它们将无法使用。



但是 - 如果您可能经常查看这些数据然后创建查看 [ ^ ]数据，例如

 CREATE VIEW dynamicTable AS 
选择[表名]，SF。* 
 FROM SchemaModel SM 
 INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID 

虽然视图实际上不存在，但您可以像查询任何其他表一样查询它

 select * from dynamicTable 

包括能够加入它

 选择 * 来自 dynamicTable DT 
  INNER  <跨度类= 代码关键字> JOIN  SchemaFields SF <跨度类= 代码关键字>上 DT.SchemaID = SF.SchemaID 

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

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

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

解决方案

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

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

这篇关于如何使用两个基表创建动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！