import table

Problems, tips, hints - all about usage.

import table

Postby code19 » Sun Nov 13, 2011 12:47 am

Hi

Great program, thank you very much

One thing i need to know.

Is it possible to import table/s (or copy) from another database?
Also a dragAndDrop of databases to sqliteStudio would be nice!

greeting
code19
 
Posts: 7
Joined: Sun Nov 13, 2011 12:27 am

Re: import table

Postby Googie » Mon Nov 14, 2011 8:52 am

Drag&drop is also in TODO for 2.1.0, but I don't know if it will be in 2.1.0 or in 2.2.0. We will see.

Importing data to table is done in 2.1.0 (yet to be released).
For now you can use custom SQL functions to do the job. What kind of file do you want to import from? I can help you with writing the function.
Googie
Site Admin
 
Posts: 309
Joined: Wed Jul 27, 2011 8:04 pm

Re: import table

Postby code19 » Mon Nov 14, 2011 1:27 pm

Thanx for replay

For now you can use custom SQL functions to do the job. What kind of file do you want to import from? I can help you with writing the function.


i only noticed, that i need sometimes to import (copy) one table (only structure or structure+rows) from another sqlite database that i done before.
i dont know if its possible to do this with a function which fits to different kind of tables?
--------------------------
The vacuum is a nice utility which you already included in sqliteStudio.
Also "DELETE FROM SQLITE_SEQUENCE;" is simple function and can be added to the rightClick menu for every table or whole database.
(only an idea, no need to bother your self with it, if its not important. im only thinking about developer who are new in SQLite)
---------------------
Drag&drop is also in TODO for 2.1.0, but I don't know if it will be in 2.1.0 or in 2.2.0. We will see.


Yahh i love sqliteStudio more and more :)
code19
 
Posts: 7
Joined: Sun Nov 13, 2011 12:27 am

Re: import table

Postby Googie » Mon Nov 14, 2011 9:51 pm

You can import table from other SQLite database in several ways.

Solution 1)
Add your original database (the one with table to copy) to SQLiteStudio. Open it. Be sure to have the new database open as well. Right-click on table to copy and pick "Create similar table". In table dialog change the database to new database, then enter the table name and push "Create". Now you have same table in new database. You just need to copy data. You can do it with SQL editor (select new database in SQL editor window):
Code: Select all
INSERT INTO newTable SELECT FROM oldDatabase.oldTable;

SQliteStudio will automatically attach oldDatabase to newDatabase and will copy all data for you. Note, that "oldDatabase" is the name as you registered your old database with in SQLiteStudio, that is the same name as you see in databases list on the left side.

Solution 2)
You can import entire database schema - all tables, indexes, triggers and views. Right-click on your new database and pick "Import schema from other database", then pick your old database and it's done. Then you have to just copy data you need. You can use method from above.

Solution 3)
The most primitive way to copy table structure is to open your old database, open old table, go to "DDL" tab, copy its contents and paste it into SQL editor and execute it on new database. Then copy data just like I wrote in Solution 1.

Yes, there's not automatic way to copy table data from one database to another. I didn't think it's that common action to perform. Maybe I will add option for that in future.

Now, about "DELETE FROM SQLITE_SEQUENCE;" - I haven't seen this kind of SQL before. What does it actually do? (I assume that SQLITE_SEQUENCE is not a regular table created by user).
Googie
Site Admin
 
Posts: 309
Joined: Wed Jul 27, 2011 8:04 pm

Re: import table

Postby code19 » Tue Nov 15, 2011 1:07 am

hi googie

thx for explanation, it helped me further
----------------------
I assume that SQLITE_SEQUENCE is not a regular table created by user

Right :)

http://www.sqlite.org/autoinc.html
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error.

if i have 1000 rows (id from 1 to 1000) and i deleted them, the autoincrement still counts along 1001, 1002 etc.
By using (DELETE FROM SQLITE_SEQUENCE WHERE NAME = 'table_name';) the autoincrement will start from 1 again.
DELETE FROM SQLITE_SEQUENCE; will effect the whole database.

greeting
code19
 
Posts: 7
Joined: Sun Nov 13, 2011 12:27 am

Re: import table

Postby Googie » Tue Nov 15, 2011 3:40 pm

I see. I guess one can use "UPDATE sqlite_sequence SET ..." to reset sequence for individual tables.
I had this in TODO as well ;)

Thanks for explanation.
Googie
Site Admin
 
Posts: 309
Joined: Wed Jul 27, 2011 8:04 pm


Return to Usage

Who is online

Users browsing this forum: No registered users and 1 guest