4
I would like to generate a table creation script, from an SQL command so I could build a program that was able to clone tables.
I am using SQL Server 2008 R2 and need to implement a table cloning routine, within an application written in VB.Net.
That way, I would choose the bank and the tables of this bank that would be cloned and then I would throw them in another database that would receive this mirroring.
12
There are numerous ways to do it, some more up to standard, some less so, some simpler, some more specific but I think this is what you’re looking for:
CREATE TABLE tabela_nova AS
(SELECT * FROM tabela_existente);
I don’t know if it works in all databases (tested in Postgresql), but you didn’t specify any. Note that only copies the table, nothing that may be related to it.
If you just want to copy the structure without copying the data just put a condition that is certainly false:
CREATE TABLE tabela_nova AS
(SELECT * FROM tabela_existente WHERE 1=2);
To the SQL Server can be:
SELECT * INTO tabela_nova From tabela_existente WHERE 1=2;
Since the above code doesn’t seem to work on Mysql, maybe it only works on it:
CREATE TABLE tabela_nova LIKE tabela_existente;
In Sqlite it is to work in the standard way but can have some side effects like loss of types. There is another way. You can take how the original table was created easily in Sqlite itself. It stores the SQL command that was used to create the original. From there you can adapt to create your new table. The data is saved in the table sqlite_master:
SELECT sql FROM sqlite_master WHERE type='table' AND name='mytable';
I tried here in Mysql not this to bumbar. It would be good to indicate where you did @bigown.
I didn’t and I said I don’t know which banks will work, but this is pretty basic SQL. Since Mysql does not follow the pattern much, I imagined that it would not even work.
A doubt (below this one). So even relationships and everything associated is "copied"?
No, this just copies the table itself, copies nothing else, indexes, nothing.
Thanks @bigown.
The indexes will yes, @Cold: "Use LIKE to create an Empty table based on the Definition of Another table, including any column Attributes and Indexes defined in the original table" (http://dev.mysql.com/doc/refman/5.1/en/create-table.html).
@bfavaretto was reading now that the Mysql method goes even but the original one I used won’t.
@bigown ran on sqlserver guy, thanks! :D
Browser other questions tagged sql database sql-server script table
You are not signed in. Login or sign up in order to post.
You can explain better what you want and give an example to understand better?
– Sergio
Friend, as @Sergio said, detail more your problem. Yes is completely possible, it all depends on what you are using. Mysql? Sql_server? Oracle? Then we can help you better.
– Fernando A.W.
as @Fernandoa. W said is possible but depends on each sample bank Sqlite would be like this
PRAGMA table_info(nome_tabela);Although for others it is similar too, just give a read in the documentation of the bank that uses.– claudsan
Does it need to be through an SQL command? And what basis are you using? If it is Mysql, the program
mysqldump(which comes with Mysql) allows you to export the structure of a table or the whole database.– bfavaretto