Creating database from DDL statements in an sql

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a file of DDL statements that I want to execute. Is there an easy way
of doing this? I have found that if I start a new query, there is a DDL
option but it only lets me copy and paste one statement at a time, which is a
bit laborious.
Thanks
 
What sort of file? Is it a structured format such as a database or a
spreadsheet, or an unstructured format such as a free-form text file? Is
there a consistent and unambiguous separator between each SQL statement in
the file?
 
It is a generated sql file from a design program, but its pretty similar to a
handcrafted creation script.

A sample of part of the file is:

-- Create a Database table to represent the "Card" entity.
CREATE TABLE Card(
CardName VARCHAR(50) NOT NULL,
RulesText INTEGER,
fk1_TypeCode INTEGER NOT NULL,
fk2_ColourCode VARCHAR(1) NOT NULL,
-- Specify the PRIMARY KEY constraint for table "Card".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT pk_Card PRIMARY KEY (CardName)
);

-- Create a Database table to represent the "Expansion" entity.
CREATE TABLE Expansion(
Code VARCHAR(3) NOT NULL,
ExpansionName VARCHAR(50) NOT NULL,
ExpansionSymbol BLOB,
-- Specify the PRIMARY KEY constraint for table "Expansion".
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT pk_Expansion PRIMARY KEY (Code)
);

-- Create a Database table to represent the "SetList" entity.
CREATE TABLE SetList(
CardNo INTEGER,
FlavourText VARCHAR(120),
fk1_Code VARCHAR(3) NOT NULL,
fk2_CardName VARCHAR(50) NOT NULL,
fk3_ArtistCode INTEGER NOT NULL,
fk4_RarityCode VARCHAR(1) NOT NULL
);

If I were using oracle I would just go into sqlplus and enter @filename
where filename is the name of the sql file with the commands in it. The
commands would execute and the tables would be created. Is there a similar
way of doing this in Access?
 
torch_music said:
It is a generated sql file from a design program, but its pretty similar to a
handcrafted creation script.
Click to expand...

In Jet, you must execute each distinct SQL statement separately :(

There is no built-in tool to execute a DDL script :(

The code required to parse a text file using the semi colon to separate
each statement using the semi colon is trivial. You could then execute
each statement against a connection in code; again, trivial :)

Note that you will have problems because you have written SQL syntax
which Jet does not possess e.g. BLOB is SQL-99 and comments are SQL-92
but Jet is not compliant with either standard :(
 
In addition to the points already made elsewhere in this thread, JET SQL
does not support comments. You're going to have to make significant
modifications to those SQL statements before they'll be compatible with JET.
As you need to make those modifications anyway, you might as well remove the
line breaks so that each SQL statement is on one line. Then you'll be able
to read them using Line Input statements and won't have to parse them.
 
OK, thanks guys.

Brendan Reynolds said:
In addition to the points already made elsewhere in this thread, JET SQL
does not support comments. You're going to have to make significant
modifications to those SQL statements before they'll be compatible with JET.
As you need to make those modifications anyway, you might as well remove the
line breaks so that each SQL statement is on one line. Then you'll be able
to read them using Line Input statements and won't have to parse them.
Click to expand...
 
Back
Top