none
subir una tabla a SQL de mas de 1024 columnas RRS feed

  • Pregunta

  • Hola, tengo una duda ¿es posible subir una tabla de mas de 1024 columnas a SQL?

    mi tabla esta en formato CSV tiene un total de 1136 columnas,  la intente subir por medio de  Wizard pero me marco el error de que supera el limite permitido que es 1024 columnas, ¿se puede subir una tabla de mas de 1024? y ¿Cómo se podría subir?

    Saludos


    :)

    jueves, 29 de octubre de 2020 19:25

Respuestas

  • Para tu ejemplo.

    Creo un csv que tiene dos filas, numeradas. Fila 1 ,numero. Fila dos letra a|b|c|d

    Ahora en TSql, como el código es muy grande, te pongo las primeras columnas ... y las últimas.

    Para separar utilizo dos funciones, que anexo ahora.

    -- Code 8.0
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums]
    (@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY
    (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
     
    GO

    y tb.

    -- Code 8.1
    -- Erland's split_me
    CREATE FUNCTION [dbo].[ufn_inline_split_me]
    (
    @param nvarchar(MAX), 
    @delimiter nchar(1) = N','
    )
    RETURNS table AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY n) AS pos,
        LTRIM(RTRIM(CONVERT(nvarchar(4000),
        SUBSTRING(@param, n,
        CHARINDEX(@delimiter, 
            @param + CONVERT(nvarchar(MAX), 
        @delimiter), n) - n)))) 
        AS Value
    FROM
        dbo.ufn_GetNums(1, LEN(@param))
    WHERE
        SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) 
       + @param, n, 1) 
        = @delimiter
    );
    GO

    Estas dos funciones. una llama a la otra, me sirven para separar por los puntos y comas (podría ser cualquier separador)

    Create table dbo.tableColumns (id int identity(1,1), col1 varchar(100),col2	 varchar(100) 
    --..... 
     ,col999	 varchar(100) ,col1000 varchar(100));
    
    Create table dbo.tableColumns2 (id int identity(1,1), col1001	 varchar(100) ,col1002	 varchar(100) ,col1003	 varchar(100) 
    -- ....
    ,col1135	 varchar(100) ,col1136 varchar(100));

    El código de inserción.

    DECLARE @CsvLocation NVARCHAR(2048)= N'c:\ejemplo\moreColumns.csv';
     
    DROP TABLE IF EXISTS #tempData; 
     
    CREATE TABLE #tempData(texto NVARCHAR(MAX)); 
     
    DECLARE @cmd NVARCHAR(2048)= 
    N'BULK INSERT #tempData'
       + N' FROM '''
       + @CsvLocation 
       + N''''
       + N' WITH'
       + N' (FIRSTROW = 1, 
            ROWTERMINATOR = ''\n'')';  
     
    EXEC sp_executesql @cmd; 
    Insert Into dbo.tablecolumns(col1
    						   , col2
    						   , col3
    						   , col4
    -- ......
    	   , col998
    						   , col999
    						   , col1000)
    Select *
    	   From (
    			  Select v.value
    				   , Row_Number() Over(Partition By t.texto
    					 Order By (Select Null) ) As fila
    					 From #tempdata As t
    						  Cross Apply dbo.ufn_inline_split_me
    						  ( t.texto, ';' ) As v
    			) As source Pivot(Max(value) For fila In([1]
    												   , [2]
    												   , [3]
    ---- ....
    												   , [998]
    												   , [999]
    												   , [1000])) As pvt;
    
    -- Ahora la segunda tabla de las columnas 1000 a 1136
    
    
    Insert Into dbo.tablecolumns2(col1001
    							, col1002
    							, col1003
    							, col1004
    -- ...
    							, col1134
    							, col1135
    							, col1136)
    Select *
    	   From (
    			  Select v.value
    				   , Row_Number() Over(Partition By t.texto
    					 Order By (Select Null) ) As fila
    					 From #tempdata As t
    						  Cross Apply dbo.ufn_inline_split_me
    						  ( t.texto, ';' ) As v
    			) As source Pivot(Max(value) For fila In([1001]
    												   , [1002]
    												   , [1003]
    --- ....
    
    												   , [1133]
    												   , [1134]
    												   , [1135]
    												   , [1136])) As pvt;
    
    

    El resultado

    select t.id, t.col1, t.col2, t.col3, t.col999, t.col1000 from tableColumns t
    select t.id, t.col1001, t.col1135, t.col1136 from tableColumns2 t

    El proceso es el mismo que puedes ver en

    Leer csv

    https://javifer2.wordpress.com/2019/11/16/leer-archivo-de-texto-o-csv-e-insertar-en-tabla/

    • Marcado como respuesta Augusto Mejia lunes, 9 de noviembre de 2020 19:08
    sábado, 31 de octubre de 2020 8:43
  • El resumen es que no, no puedes tener más de 1024 columnas en una tabla.

    En respuestas anteriores te han sugerido dividirlo en dos tablas, e incluso te han propuesto un ejemplo de cómo hacerlo.

    Yo te voy a proponer otra alternativa diferente para guardar más de 1024 datos en una tabla: Declara una columna del tipo XML o del tipo JSON si tu SQL Server es lo bastante moderno para soportar este último tipo de dato.

    De tu CSV, elige cuáles son las columnas más importantes para ponerlas como columnas en la tabla de destino (1023 como máximo, pero seguramente te podrás apañar con muchas menos). Y el resto de las columnas, agrúpalas en un objeto de tipo XML o JSON, y guarda ese objeto dentro del campo XML o JSON en tu tabla.

    No hay un automatismo para esto en el asistente; tendrías que escribir algo de código para hacer esta conversión. Pero el resultado será muy satisfactorio; si escribes correctamente tus consultas, podrás hacer un Select que extraiga datos de dentro de este campo y los muestre todos en forma tabular como si fuesen columnas normales, e incluso podrás filtrar por ellos de manera razonablemente eficiente si añades índices de tipo XML.


    sábado, 31 de octubre de 2020 13:31

Todas las respuestas

  • Hola amigo,

    Realmente no será posible colocar más de 1024 columnas.
    Tendrá que intentar fusionar columnas o dividir en otras tablas.

    Incluso te recomiendo compartir si es posible.

    Si esta respuesta le ayudó, márquela como útil para que otra persona con una pregunta o problema similar pueda encontrar una respuesta o ayudar más fácilmente. * Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de bases de datos - Sql Server y Oracle]
    sábado, 31 de octubre de 2020 1:59
  • Hola Augusto Mejía:

    El limite de columnas en SQL Server es de 1024. 

    https://docs.microsoft.com/es-es/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

    Puedes hacerlo leyendo todo el csv en una sola columna de una tabla temporal. 

    Luego de la tabla temporal extraes mediante alguna función para separar cadenas las filas, e insertas en las tablas de destino, al menos dos, los resultados partidos.

    Tienes un ejemplo de como se realiza esto en el siguiente artículo

    Leer csv

    https://javifer2.wordpress.com/2019/11/16/leer-archivo-de-texto-o-csv-e-insertar-en-tabla/

    Lo único que tienes que cambiar, es que tienes que hacer dos inserts, pero como los datos los tienes en una tabla temporal, solo tienes que escoger unas u otras columnas.

    sábado, 31 de octubre de 2020 7:58
  • Para tu ejemplo.

    Creo un csv que tiene dos filas, numeradas. Fila 1 ,numero. Fila dos letra a|b|c|d

    Ahora en TSql, como el código es muy grande, te pongo las primeras columnas ... y las últimas.

    Para separar utilizo dos funciones, que anexo ahora.

    -- Code 8.0
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums]
    (@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY
    (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
     
    GO

    y tb.

    -- Code 8.1
    -- Erland's split_me
    CREATE FUNCTION [dbo].[ufn_inline_split_me]
    (
    @param nvarchar(MAX), 
    @delimiter nchar(1) = N','
    )
    RETURNS table AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY n) AS pos,
        LTRIM(RTRIM(CONVERT(nvarchar(4000),
        SUBSTRING(@param, n,
        CHARINDEX(@delimiter, 
            @param + CONVERT(nvarchar(MAX), 
        @delimiter), n) - n)))) 
        AS Value
    FROM
        dbo.ufn_GetNums(1, LEN(@param))
    WHERE
        SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) 
       + @param, n, 1) 
        = @delimiter
    );
    GO

    Estas dos funciones. una llama a la otra, me sirven para separar por los puntos y comas (podría ser cualquier separador)

    Create table dbo.tableColumns (id int identity(1,1), col1 varchar(100),col2	 varchar(100) 
    --..... 
     ,col999	 varchar(100) ,col1000 varchar(100));
    
    Create table dbo.tableColumns2 (id int identity(1,1), col1001	 varchar(100) ,col1002	 varchar(100) ,col1003	 varchar(100) 
    -- ....
    ,col1135	 varchar(100) ,col1136 varchar(100));

    El código de inserción.

    DECLARE @CsvLocation NVARCHAR(2048)= N'c:\ejemplo\moreColumns.csv';
     
    DROP TABLE IF EXISTS #tempData; 
     
    CREATE TABLE #tempData(texto NVARCHAR(MAX)); 
     
    DECLARE @cmd NVARCHAR(2048)= 
    N'BULK INSERT #tempData'
       + N' FROM '''
       + @CsvLocation 
       + N''''
       + N' WITH'
       + N' (FIRSTROW = 1, 
            ROWTERMINATOR = ''\n'')';  
     
    EXEC sp_executesql @cmd; 
    Insert Into dbo.tablecolumns(col1
    						   , col2
    						   , col3
    						   , col4
    -- ......
    	   , col998
    						   , col999
    						   , col1000)
    Select *
    	   From (
    			  Select v.value
    				   , Row_Number() Over(Partition By t.texto
    					 Order By (Select Null) ) As fila
    					 From #tempdata As t
    						  Cross Apply dbo.ufn_inline_split_me
    						  ( t.texto, ';' ) As v
    			) As source Pivot(Max(value) For fila In([1]
    												   , [2]
    												   , [3]
    ---- ....
    												   , [998]
    												   , [999]
    												   , [1000])) As pvt;
    
    -- Ahora la segunda tabla de las columnas 1000 a 1136
    
    
    Insert Into dbo.tablecolumns2(col1001
    							, col1002
    							, col1003
    							, col1004
    -- ...
    							, col1134
    							, col1135
    							, col1136)
    Select *
    	   From (
    			  Select v.value
    				   , Row_Number() Over(Partition By t.texto
    					 Order By (Select Null) ) As fila
    					 From #tempdata As t
    						  Cross Apply dbo.ufn_inline_split_me
    						  ( t.texto, ';' ) As v
    			) As source Pivot(Max(value) For fila In([1001]
    												   , [1002]
    												   , [1003]
    --- ....
    
    												   , [1133]
    												   , [1134]
    												   , [1135]
    												   , [1136])) As pvt;
    
    

    El resultado

    select t.id, t.col1, t.col2, t.col3, t.col999, t.col1000 from tableColumns t
    select t.id, t.col1001, t.col1135, t.col1136 from tableColumns2 t

    El proceso es el mismo que puedes ver en

    Leer csv

    https://javifer2.wordpress.com/2019/11/16/leer-archivo-de-texto-o-csv-e-insertar-en-tabla/

    • Marcado como respuesta Augusto Mejia lunes, 9 de noviembre de 2020 19:08
    sábado, 31 de octubre de 2020 8:43
  • El resumen es que no, no puedes tener más de 1024 columnas en una tabla.

    En respuestas anteriores te han sugerido dividirlo en dos tablas, e incluso te han propuesto un ejemplo de cómo hacerlo.

    Yo te voy a proponer otra alternativa diferente para guardar más de 1024 datos en una tabla: Declara una columna del tipo XML o del tipo JSON si tu SQL Server es lo bastante moderno para soportar este último tipo de dato.

    De tu CSV, elige cuáles son las columnas más importantes para ponerlas como columnas en la tabla de destino (1023 como máximo, pero seguramente te podrás apañar con muchas menos). Y el resto de las columnas, agrúpalas en un objeto de tipo XML o JSON, y guarda ese objeto dentro del campo XML o JSON en tu tabla.

    No hay un automatismo para esto en el asistente; tendrías que escribir algo de código para hacer esta conversión. Pero el resultado será muy satisfactorio; si escribes correctamente tus consultas, podrás hacer un Select que extraiga datos de dentro de este campo y los muestre todos en forma tabular como si fuesen columnas normales, e incluso podrás filtrar por ellos de manera razonablemente eficiente si añades índices de tipo XML.


    sábado, 31 de octubre de 2020 13:31
  • Ya se que ya bastantes han opinado aquí y no tengo nada contra las recomendaciones que ne general creo que son correctas y adecuadas.  Intervengo solo  para aclarar un punto: en Sql server si que se pueden crear tablas con mas de 1024 columnas y se les conoce como "Wide Tables" y pemite crear tablas de hasta 30.000 columnas. Lo que si no se puede evitar es el limite de 8060 caracteres por registro.

    Para solucionar el problema se usan columnas "Sparse" que permiten optimizar el almacenamiento si se tienen valores null.  En este caso particular que se desean crear 1136 columnas usar columnas sparse parece perfectamente viable.

    sábado, 31 de octubre de 2020 16:16
  • Hola amigo,

    Reconozco la posibilidad de columnas 'dispersas' usando NULL. Sin embargo, en términos de rendimiento, tendrás problemas.
    Nunca había visto una mesa tan grande, incluso en DAtaware House, y creo que incluso puede ser un problema de diseño.
    Está claro que como respuesta existe la posibilidad, pero se deben evaluar todos los puntos.
    * Recordando que las columnas 'SPARSE' tienen algunos 'efectos negativos'.
    Mayor espacio de almacenamiento para valores no NULL y sobrecarga para recuperar valores no NULL.
    Hay restricciones de tipo de datos

    para que el amigo de la pregunta pueda responder más preguntas, siga el enlace en las columnas 'SPARSE'

    Usar columnas dispersas


    Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    sábado, 31 de octubre de 2020 17:42