Create table for each lines SQL

Question

I have a .txt file with a few thousand lines. On every line, there is the name of a table. For example, this is an extract of the text file:

dog

cat

elephant

banana

blueberry

.......

I want to create tables with the title dog, cat, elephant...

How can I achieve this and add all the tables to my Database?

Thanks for your help!

*"All of my tables are stored in Adminer(similar to phpMyAdmin)."* -- the tables are stored in the database. Adminer and phpMyAdmin (and many others) are just tools used to view and manipulate the data stored in the database. Nothing is stored in Adminer or phpMyAdmin (except for their own configuration but they are not relevant to the question). — axiac, Aug 17 '17 at 13:43
okay but do you think that you can help me with my question? — alb, Aug 17 '17 at 13:50
Have you tried anything yet? Why would you even want to do this? this is most certainly not a good idea — Milney, Aug 17 '17 at 13:53
because I have information about many websites and I want each website to have its own table. — alb, Aug 17 '17 at 13:55
Use Adminer to create one table, see what SQL query it generates for the creation (it looks like [`CREATE TABLE ...`](https://dev.mysql.com/doc/refman/5.7/en/create-table.html)) and use the search & replace functionality of your favorite text editor to repeat it for every line in your text file. — axiac, Aug 17 '17 at 14:15
Why not to create one single table to store information about those websites ? One line per website, and specify a column for each information type. — Canuto-C, Aug 17 '17 at 15:39
One thousand identical tables is bad design. This discussion has been replayed many times on this forum. — Rick James, Aug 22 '17 at 00:49

score 0 · Answer 1 · answered Aug 17 '17 at 16:22

If all the table structures are exactly the same, then you could use a combination of a cursor and dynamic sql. After you upload the text file to a table, you can use a cursor to loop through each one.

CREATE TABLE #temp
(TableName NVARCHAR(20))
INSERT INTO #temp
VALUES ('Dog'),('Cat'),('Mouse')

DECLARE @mySQL NVARCHAR(MAX);
DECLARE @name NVARCHAR(50);

DECLARE crsr CURSOR FOR
SELECT TableName FROM #temp

OPEN crsr 
FETCH NEXT FROM crsr INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @mySQL = 'CREATE TABLE dbo.' + @name + ' (Col_1 CHAR(10), Col_2 INT)'
    PRINT @mySQL

    EXECUTE (@mySQL)

    FETCH NEXT FROM crsr INTO @name

END 

CLOSE crsr
DEALLOCATE crsr

Create table for each lines SQL

1 Answers1