Using a script to create tables within Webmatrix RSS

• • Reply
  

  DMT20601

  Member

  86 Points

  197 Posts

  Using a script to create tables within Webmatrix

  Dec 24, 2012 01:31 PM|LINK

  I have discovered that I can use the query feature  on the IDE ribbon to execute a CREATE TABLE script.  With that in mind I plan to build  12 tables with a script.  If I can create than I should be able to INSERT, DELETE, MODIFY and maybe ALTER, etc....  The easy part is the syntax for CREATE TABLE, but how to code (script)  the CONSTRAINTS for the Primary Key and Foriegn keys.  Is there a special naming convention or structure  for naming the  indexes or can you use anything like [Test_PK_Testid]  PRIMARY KEY.

  Does anybody use scripts to build tables with keys for PK and FK and Composit (link table)?

  Thanks Dallas

  This almost works:

  CREATE TABLE [Test]
  {
      [TestID] [int] IDENTITY NOT NULL,
      [Field1] [nvarchar] (20) NULL,
      [Field2] [money] NULL,
      [CategoryID] [int] NULL,
      CONSTRAINT [????] PRIMARY ???
  }

  • Edited by XIII on Dec 24, 2012 01:45 PM
  • Moved by XIII on Dec 24, 2012 01:45 PM
• • Reply
  

  rrrsr7205

  Participant

  1304 Points

  313 Posts

  Re: Using a script to create tables within Webmatrix

  Dec 24, 2012 01:54 PM|LINK

  Check out the SQL CE 4.0 Standalone App (http://sqlcetoolbox.codeplex.com/releases).  It is launchable from the command line and has  scripting support for tables in an existing DB.

  You should also check the TSQL references on MSDN: http://msdn.microsoft.com/en-us/library/ms173372.aspx

  (e.g.:

  CREATE TABLE table_name
  ( { < column_definition > | < table_constraint > } [ ,...n ]
  )
  < column_definition > ::=
  { column_name data_type }
  [ { DEFAULT constant_expression
  | [ IDENTITY [ ( seed , increment ) ]
  ]
  } ]
  [ ROWGUIDCOL ]
  [ < column_constraint > [ ...n ] ]
  < column_constraint > ::=
  [ CONSTRAINT constraint_name ]
  { [ NULL | NOT NULL ]
  | [ PRIMARY KEY | UNIQUE ]
  | REFERENCES ref_table [ ( ref_column ) ]
  [ ON DELETE { CASCADE | NO ACTION } ]
  [ ON UPDATE { CASCADE | NO ACTION } ]
  }
  < table_constraint > ::=
  [ CONSTRAINT constraint_name ]
  { [ { PRIMARY KEY | UNIQUE }
  { ( column [ ,...n ] ) }
  ]
  | FOREIGN KEY
  ( column [ ,...n ] )
  REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
  [ ON DELETE { CASCADE | NO ACTION } ]
  [ ON UPDATE { CASCADE | NO ACTION } ]
  }

  )

  RRR
• • Reply
  

  Careed

  All-Star

  18764 Points

  3637 Posts

  Re: Using a script to create tables within Webmatrix

  Dec 24, 2012 01:56 PM|LINK

  Try something like this:

  CREATE TABLE [Test]
  {
      [TestID] [int] IDENTITY PRIMARY KEY,
      [Field1] [nvarchar] (20) NULL,
      [Field2] [money] NULL,
      [CategoryID] [int] NULL REFERENCES Category(ID)
  }

  You may need to work with the syntax a little to get it to work for your needs.

  Also, for more information, see the following link:

  http://msdn.microsoft.com/en-us/library/ms174979.aspx

  
   

  Christopher Reed, MCT, MCPD, MCTS, Microsoft Specialist, MTA
  "The oxen are slow, but the earth is patient."
  • Marked as answer by DMT20601 on Jan 02, 2013 01:38 PM
• • Reply
  

  rrrsr7205

  Participant

  1304 Points

  313 Posts

  Re: Using a script to create tables within Webmatrix

  Dec 24, 2012 02:06 PM|LINK

  Check out the SQL CE 4.0 Standalone App (http://sqlcetoolbox.codeplex.com/releases).  It is launchable from the command line and has  scripting support for tables in an existing DB.

  You should also check the TSQL references on MSDN: http://msdn.microsoft.com/en-us/library/ms173372.aspx

  (e.g.:

  CREATE TABLE table_name
  ( { < column_definition > | < table_constraint > } [ ,...n ]
  )
  < column_definition > ::=
  { column_name data_type }
  [ { DEFAULT constant_expression
  | [ IDENTITY [ ( seed , increment ) ]
  ]
  } ]
  [ ROWGUIDCOL ]
  [ < column_constraint > [ ...n ] ]
  < column_constraint > ::=
  [ CONSTRAINT constraint_name ]
  { [ NULL | NOT NULL ]
  | [ PRIMARY KEY | UNIQUE ]
  | REFERENCES ref_table [ ( ref_column ) ]
  [ ON DELETE { CASCADE | NO ACTION } ]
  [ ON UPDATE { CASCADE | NO ACTION } ]
  }
  < table_constraint > ::=
  [ CONSTRAINT constraint_name ]
  { [ { PRIMARY KEY | UNIQUE }
  { ( column [ ,...n ] ) }
  ]
  | FOREIGN KEY
  ( column [ ,...n ] )
  REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
  [ ON DELETE { CASCADE | NO ACTION } ]
  [ ON UPDATE { CASCADE | NO ACTION } ]
  }

  )

  You might also want to download SQL Server Management Studio and SQL Server Express.  The management studio will support any of your scripting needs.  Just be careful not to use the bells and whistles that SQL Ce does not support.  You generate the script then run it in a query window for SQL CE

  RRR
• • Reply
  

  DMT20601

  Member

  86 Points

  197 Posts

  Re: Using a script to create tables within Webmatrix

  Jan 02, 2013 01:38 PM|LINK

  Thanks,  you pointed me in the right direction,  I expanded your help.  This script does work in Webmatrix.

  CREATE TABLE Contrib( 
         ContribId    int IDENTITY NOT NULL CONSTRAINT pk_Contrib PRIMARY KEY,
         MemberId     int          NOT NULL CONSTRAINT fk_Contrib_Member    REFERENCES Member(MemberId),
         NpId         int          NOT NULL CONSTRAINT fk_Contrib_Nonprofit REFERENCES Nonprofit(NpId),
         EventId      int          NOT NULL CONSTRAINT fk_Contrib_Event     REFERENCES Event(EventId),
         AdditionalAmount Money,
         TotalAmount      Money,
         ContribDate      DateTime,
         DisbursedDate    DateTime,
         Comment          nVarChar (50) )