Execute create table query from column in SQL server

Question

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

Comments are not for extended discussion; this conversation has been moved to chat. — Samuel Liew
– Samuel Liew, Commented Sep 27, 2022 at 6:09

Amal Ps · Accepted Answer · 2022-09-25 16:28:08Z

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 :)

Serg · Accepted Answer · 2022-09-25 14:09:35Z

0

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.

answered Sep 25, 2022 at 14:09

Serg

22.8k5 gold badges23 silver badges50 bronze badges

4 Comments

Sriram NR Over a year ago

The tables are created dynamically so i cant use into for this

Sriram NR Over a year ago

I am just looking to execute the queries within the two columns

Serg Over a year ago

Use a cursor to iterate over the table containing 2 columns of sql text to be executed dynamically.

Sriram NR Over a year ago

Yes this is what i was thinking to do. But not sure. Will check it out

Collectives™ on Stack Overflow

Execute create table query from column in SQL server

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related