动态生成表. [英] Dynamic generation of table.

问题描述

你好朋友，

我有一个已创建的表的要求，我想在运行时创建一个新表.

但是，表名称应类似于
已经创建的表名称="Balance0"
要创建的新表="Balance1"

当我使用以下查询创建新表时

Hello friends,

I have a requirement in which i have one table already created and i want to create a new table in runtime.

But, table name should be like
Already created table name = "Balance0"
new table to be create = "Balance1"

When i create new table using following query

SELECT * INTO Balance1 FROM Balance0

正常工作

但是我希望新表名称始终以增量形式出现，即"Balance0"，"Balance1"，"Balance2"等等.

因此，首先我从数据库中获取上一个表名称，即"Balance0"，并通过对其应用字符串操作将最后一位数字加1，因此新表名称将类似于"Balance1".
为此，我必须将所有这些操作都放在一个字符串变量中，当我尝试以下代码时，它给我错误

it works fine

but i want new table name always in incremental form i.e "Balance0","Balance1","Balance2",...and so on.

so first i take the previous table name i.e "Balance0" from database and increments the last digit by 1 by using applying string operations on it so new table name would be like "Balance1".
To do this i have to take all this operation in a string variable and when i try the following code it gives me error

SELECT * INTO @newTablename FROM Balance0

由于我们不能使用任何变量代替表名.

有人可以告诉我如何解决这个问题吗?

提前谢谢.
Shainy
:-)

As we can not use any variable in place of table name.

Can anybody tell me how to solve this problem????


Thanks in advance.
Shainy
:-)

推荐答案

查找动态SQL
这是一个示例:

Look up dynamic SQL
Here is an example:

DECLARE @mysql VARCHAR(2000);
SET @mysql = ''SELECT * INTO '' + @newTableName + '' FROM '' + @oldTableName; 
EXECUTE sp_executesql(@mysql);

使其成为动态SQL

Make it dynamic SQL

DECLARE @sql NVARCHAR (MAX)
SET @sql = 'SELECT * INTO ' + @newTablename + ' FROM Balance0 '

EXEC (@sql)

可以像已经给出的示例一样动态地执行此操作，如果需要这样做，我会重新考虑.在描述声音时动态创建表，例如对数据进行分区等.我相信像这样创建新表不是可行的解决方案，而是考虑在表中添加更多列以描述数据或使用触发器移动/根据实际需求复制数据等.

While you can do this dynamically as in the examples already given, I would reconsider if this is necessary. Creating dynamically tables as you describes sounds like you''re for example partitioning data etc. I believe that creating new table like this isn''t a feasible solution, instead consider adding more columns to the table to describe the data or use triggers to move/copy data etc. depending on the actual requirements.

这篇关于动态生成表.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！