CODEWITHSUNDEEP
sqlsql-servert-sql
Aleb
Aleb

Reputation: 85

T-SQL how to create a table whose structure is in another table?

How can I create a stored procedure to create a table, whose structure is stored in another table, with t-sql of course and sql server?

Upvotes: 3

Views: 157

Answers (2)

Vasyl Zvarydchuk
Vasyl Zvarydchuk

Reputation: 3839

You can construct query as string with CREATE TABLE statement and execute it by using sp_executesql stored procedure.

For instance in this way:

DECLARE @query nvarchar(max) = N'CREATE TABLE Table(Col nvarchar(50) NULL)'
EXECUTE sp_executesql @query

where in the @query variable you can build based on what kind of table you need.

Upvotes: 2

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

T-SQL allows to create tables on the fly using SELECT * ... INTO syntax:

SELECT * INTO TargetTable
FROM SourceTable
-- any false returning condition will do
WHERE 1 = 0

This can also be written elegantly (thanks to Deadsheep39):

SELECT TOP 0 * INTO TargetTable
FROM SourceTable

However, this will fail if TargetTable already exists, so you should check for its existence:

IF OBJECT_ID('TheSchema.TargetTable') IS NOT NULL
    DROP TABLE TargetTable

Also, no indexes, constraints or trigger will be created. Check here for more details.

If you want to go dynamic (table names are parameters) you can create and execute a dynamic query:

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

Upvotes: 5

Related Questions