how to create a SQL table with dynamic columns

Question

how to create a SQL table with dynamic columns

Navigation

#1 by (0 votes)

0

How can I make a dynamic SP where according to the data of a table1 I take them as columns to create a table2 these may vary .

That is to say how there can be 1 or 2 there can be 10,20,30 ... I leave a SP to create a table

CREATE PROCEDURE [dbo].[sp_CrearTablaResources] 
-- Aqui van los paramentros

AS
BEGIN
SET NOCOUNT 
create table Resources(
Id int Primary key Identity(1,1),
ResourceId int not null,
ResourceName varchar(max) not 
END

sql sql-server sql-server-2014 stored-procedure

asked by Ivxn 13.04.2018 в 21:40

source

1 answer

Load activity with a data from the Firebase database Show the variable with its different value

score 0 · Answer 1

A few days ago, they asked this question about Dynamic Columns SQL-Server here in SO, maybe I can help you.

Otherwise I'll write you a small alternative solution that you can use as a guide for what you need, I think it might work.

You can create dynamic SQL something like this:

Send the name of the table as a parameter to SP
Send DataTable with NombreColumna , TipoDato to SP (table defined by the user)

To send list, datatable you need to create TABLE DEFINED BY THE USER

CREATE TYPE [dbo].[MiColumnaTipoDato] AS TABLE(
    NombreColumna [NVARCHAR](200) NULL,
    TipoDato [NVARCHAR](200) NULL
);

Then, in the SP , create the cursor to traverse the table and make your dynamic SQL statement.

Create Procedure sp_CrearTablasDinamicas(
@NombreTabla nvarchar(150),
@EsquemaTabla [MiColumnaTipoDato] readonly
)
AS BEGIN
 DECLARE @sql NVARCHAR(MAX);

 set @SQL= N' Create table '+QUOTENAME(@NombreTabla);

 set  @SQL =@SQL+ ' Aquí se repite sobre @EsquemaTabla para agregar N Columnas '

 EXECUTE sp_executesql  @sql
END

For reference QUOTENAME was specifically designed to name names of columns / tables / databases - sysnames. For example, this: SELECT QUOTENAME('abc[]def') returns [abc[]]def] while SELECT '[' + 'abc[]def' + ']' returns [abc[]def] that is not valid to use as a column / table / name of the database.