Python SQL Server - Create Table from Values from an existing Table

Question

i'm looking for a solution how to create a table from this data (which is located on a table in a sql database with the name "table1") with python or sql

tablename	column	datatype	length
a	1	varchar	5
a	2	date	0
a	3	float	0
b	1	varchar	10
b	2	varchar	8
b	3	float	0
b	4	varchar	50
c	1	date	0
c	2	varchar	6

goal is to create tables (a,b,c)

their columns and their datatype can also be found in the same table

the tricky part is, that the values of "table1" will always be different(dynamic tablenames, columnnames, datatype and length), so i need to write a code, that can reference the values in "table1" and create all tables with their columns (+datatype) found in "table1"

i got this already

DECLARE @n INT = (SELECT COUNT(DISTINCT TABNAME) FROM dbo.Tablenames),
    @i INT = '1',
    @query VARCHAR(4000)

WHILE @n >= @i
BEGIN
DECLARE @tablename VARCHAR(255) = (Select Top 1 TABNAME FROM 
dbo.Tablenames),
        @columnname VARCHAR(255), --not properly declared
        @datatype VARCHAR(255),   --not properly declared
        @length VARCHAR(255)      --not properly declared
SELECT TOP 1 @tablename
FROM dbo.Tablenames
SET @query = N'CREATE TABLE' + QUOTENAME(@tablename) + 
    '(' +
    @columnname + @datatype + '(' + @length + ')' --this part needs if statement (if not varchar = no '+ '(' + @length + ')''
    + ')'  
EXEC (@query)

SET @i+=1

DELETE FROM dbo.Tablenames
WHERE TABNAME = @tableName
END

thanks in advance

Relevant? https://stackoverflow.com/questions/29638136/how-to-speed-up-bulk-insert-to-ms-sql-server-using-pyodbc — Charlieface, Jun 09 '22 at 14:13
[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Jun 09 '22 at 14:28
@Charlieface, i'm still not at the insert part...stuck at creating tables — Cindy, Jun 10 '22 at 11:54
Something like [this](https://pastebin.com/PMNCGMu2) would be one way to tackle it. (As it is essentially dynamic SQL, be careful to avoid SQL injection vulnerabilities.) — Gord Thompson, Jun 10 '22 at 12:50

score 0 · Answer 1 · answered Jun 10 '22 at 16:21

You can use dynamic SQL for this.

You simply aggregate the whole thing using STRING_AGG. You need two levels of aggregation, once for the columns, then again for the tables. This gives you one big script you can execute.

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(CONCAT(
      'CREATE TABLE ',
      QUOTENAME(t1.tablename),
      ' (
',
      t1.columns,
      '
);'
    ), '
')
    FROM (
        SELECT
          t1.tablename,
          columns = STRING_AGG(CAST(CONCAT(
            '  ',
            QUOTENAME(t1.[column]),
            ' ',
            t1.datatype,
            CASE WHEN t1.datatype = 'varchar' THEN CONCAT('(', t1.length, ')') END
          ) AS nvarchar(max)), ',
')
        FROM table1 t1
        GROUP BY
          t1.tablename
    ) t1
);

PRINT @sql;

EXEC sp_executesql @sql;

db<>fiddle

Python SQL Server - Create Table from Values from an existing Table

1 Answers1