On 06/06/2011, at 9:41 AM, Kyle Malloy wrote:
> After creating a Database.sqlite i then create a Table and try to past text
> from a text file into the shell, but the text wraps? Im working on an iphone
> app and im new to it all. Ive been reading lots of tutorials and it seems
> that everyone builds databases this way. I have just under 4,000 lines of
> code i pre-typed in a text file. I want to be able to copy and paste it all
> into the shell.
> Example of the text I'm Pasting into Shell:INSERT INTO Datalist(staff,floor)
> VALUES(‘Dick, Tom’,2);
> Example of the text after Pasting into Shell:INSERT INTO
> Datalist(staff,floor) Dick, ,2);TomVALUES(
> Please someone help whats the best way to get all this info in a .sqlite
> database by copy past. Also if there is a good tutorial out there could i
> please get the link. Thank you Kyle
You should:
1. Use straight quotes, not smart quotes. (But I suspect they're only appearing
here in your mail message, not your importing file.)
2. Normalize your data. Don't put multiple values (eg Tom & Dick) in one
column. Instead, create a people table and another table that links multiple
people with each floor.
Something like this:
create table staff
( id integer primary key not null
, "first name" text collate nocase
, "last name" text collate nocase
, "email" text collate nocase
)
;
create table Floor
( id integer primary key not null
, "some other info, unique to each floor" text
)
;
create table "Floor Staff"
( id integer primary key not null
, floor integer not null references Floor(id)
, staff integer not null references Staff(id)
)
;
-- Then insert your data:
begin
;
insert into Staff ("first name") values ('Tom')
;
insert into Staff ("first name") values ('Dick')
;
insert into "Floor Staff" ("floor", "staff")
select 2, id from "Staff" where "first name" = 'Tom'
;
insert into "Floor Staff" ("floor", "staff")
select 2, id from "Staff" where "first name" = 'Dick'
;
commit
;
If you want to get a list of people on a particular floor, just select like
this:
select "first name"
from "Floor Staff" join "Staff" on "Floor Staff"."staff" = "Staff".id
where "Floor" = 2
;
-- or as a comma separated list:
select group_concat("first name", ', ')
from "Floor Staff" join "Staff" on "Floor Staff"."staff" = "Staff".id
where "floor" = 2
;
Tom
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
[email protected]
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
