Execute create table query from column in SQL server-CodePudding

I have two select statements which are to create table and insert values into table. How to execute all the queries inside the column at one go? Below is my code and the output:

select n.*
into #norm
from specNormalization n 

select n.* 
into #raw
from rawdata n 

select distinct 'CREATE TABLE raw'   c.desttablename   ' ('   STUFF(
             (select ',' QUOTENAME( c.[destfieldname] )   c.datatype 
             from #norm c
     group by c.destfieldname, c.datatype 
                 for xml path ('')),1,1,'') as createTableSQL 

select distinct 'INSERT INTO raw'   c.desttablename   
             select d.contents 
             from #raw d join #norm c on d.tablename = c.desttablename
     as insertTableSQL

rawdata table

desttablename	destfieldname	datatype
rawtable	SbjNum	int
rawtable	Surveyor	nvarchar(20)
rawtable	Location	nvarchar(20)
rawtable2	SbjNum	int
rawtable2	Name	nvarchar(20)
rawtable2	Address	nvarchar(20)

specnomalization table

tablename	destfieldname	contents
rawtable	SbjNum	1
rawtable	Surveyor	Alex
rawtable	Location	Georgia
rawtable2	SbjNum	1
rawtable2	Name	Sandra
rawtable2	Address	Portland

createTableSQL
CREATE TABLE rawtable ([Sbjnum])int, ([Surveyor])nvarchar(200), ([Location])nvarchar(200)
CREATE TABLE rawtable2 ([Sbjnum])int, ([Name])nvarchar(200), ([Address])nvarchar(200)

insertTableSQL
INSERT INTO rawtable SELECT [Sbjnum], [Surveyor], [Location] from #raw
INSERT INTO rawtable2 SELECT [Sbjnum], [Name], [Address] from #raw

CodePudding user response：

You can create the create query by using stuff. Since you would have multiple tables so you have to group by the query to return multiple table names and it's columns separately. So the result query you can set into a variable and can execute by using

EXEC sp_executesql

So you can easily create as much as tables based on your table data. The following query will help you to create multiple tables. This you can optimize if needed, but this will give you the insight to do the same for inserting values to the tables respectively.

Declare @sql nvarchar(MAX) = ( SELECT ' CREATE TABLE '  desttablename  ' ('   STUFF((SELECT ', '   sn.[destfieldname]   ' '  datatype
                               FROM  dbo.specNormalization As sn
                               ORDER BY sn.destfieldname
                               FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, '')   ' )'
FROM dbo.specNormalization as P2
GROUP BY P2.desttablename  FOR XML PATH('') )

-- Uncomment to see the created query
-- SELECT @sql

-- To execute the created query. This will create the tables with column and datatype
EXEC sp_executesql @sql

Hope this helps. Happy coding :)

CodePudding user response：

Too long for a comment. Try to generate SELECT .. INTO .. FROM to create and populate a target table. Kind of

    SELECT cast([Sbjnum] as int) [Sbjnum], cast([Surveyor] as nvarchar(200)) [Surveyor], cast([Location] as nvarchar(200)) [Location]
      INTO rawtable
    FROM #raw

If the source types are the same as target, skip cast functions. Can't tell what is the query to generate it as no sample data are provided.