我该如何使用T-SQL和SQL Server创建存储过程来创建一张表,其结构存储在另一张表中?
T-SQL允许使用SELECT * ... INTO
语法动态创建表:
SELECT * INTO TargetTable
FROM SourceTable
-- any false returning condition will do
WHERE 1 = 0
Deadsheep39
):SELECT TOP 0 * INTO TargetTable
FROM SourceTable
然而,如果目标表已经存在,这将会失败,因此您应该检查它是否存在:
IF OBJECT_ID('TheSchema.TargetTable') IS NOT NULL
DROP TABLE TargetTable
CREATE PROCEDURE dbo.GenerateTable
(
@SourceTable VARCHAR(128),
@TargetTable VARCHAR(128)
)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) = N'
SELECT * INTO ' + QUOTENAME(@TargetTable) + '
FROM ' + QUOTENAME(@SourceTable) + '
-- any false returning condition will do
WHERE 1 = 0'
EXEC (@SQL)
END
GO
if object_id('dbname.schema.tablename') is not null drop table dbname.schema.tablename
如果你在默认架构下的数据库中,也可以这样写: if object_id('tablename') is not null drop table tablename
或者在 tempdb 示例中如下所示:if object_id('tempdb..#tablename') is not null drop table tempdb..#tablename
。 - Deadsheep39您可以使用CREATE TABLE
语句构建查询字符串,并通过使用sp_executesql
存储过程来执行它。
例如:
DECLARE @query nvarchar(max) = N'CREATE TABLE Table(Col nvarchar(50) NULL)'
EXECUTE sp_executesql @query
您可以根据需要构建的表格类型,在@query
变量中进行构建。
select top 0 * into newtable from...
。 - Deadsheep39