Friday, March 9, 2012

How to create a Database in SQL 2005 by using a txt file.

DMI wrote:
> Good morning - I have a text file with the information on how to create a
> database, the relationship between tables, etc. I am new to SQL 2005. Wh
at
> I would like to do is to create the database and structure by using this
> following file (MYFILE.SQL) as described below.
> How can I do this?
>
Launch Management Studio, then do File -> Open -> File, navigate to your
MYFILE.SQL, load it, then click ExecuteGood morning - I have a text file with the information on how to create a
database, the relationship between tables, etc. I am new to SQL 2005. What
I would like to do is to create the database and structure by using this
following file (MYFILE.SQL) as described below.
How can I do this?
Thank you in advance for your assistance.
CREATE TABLE [dbo].[DEPT] (
[Dept_ID] [numeric](10, 0) NOT NULL ,
[DeptName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[ShortCutName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PART] (
[Part_ID] [numeric](10, 0) NOT NULL ,
[PartName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[PartFriendlyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS NOT
NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[GreenDays] [smallint] NOT NULL ,
[YellowDays] [smallint] NOT NULL ,
[RedDays] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDEPT] (
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[ProcessOrder] [int] NOT NULL ,
[CompletesProcess] [bit] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTDETAILLOG] (
[PartDetailLog_ID] [numeric](10, 0) NOT NULL ,
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[PartDept_ID] [numeric](10, 0) NOT NULL ,
[StartTime] [smalldatetime] NOT NULL ,
[CompleteTime] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTINSTRUCT] (
[PartInstruct_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[StepName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[StepValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StepNumber] [smallint] NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL ,
[ImageFTPAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL ,
[OperShowImage] [bit] NOT NULL ,
[FTPPostedDate] [smalldatetime] NULL ,
[ExtInstructionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[PARTMAINLOG] (
[PartMainLog_ID] [numeric](10, 0) NOT NULL ,
[Part_ID] [numeric](10, 0) NOT NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PartCompleted] [smalldatetime] NULL ,
[Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[LastUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[REJECT] (
[Reject_ID] [numeric](10, 0) NOT NULL ,
[DateOfReject] [smalldatetime] NOT NULL ,
[Part_ID] [numeric](10, 0) NULL ,
[Dept_ID] [numeric](10, 0) NULL ,
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[EnteredBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNum] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Revision] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ROOTCAUSE] (
[RootCause_ID] [numeric](10, 0) NOT NULL ,
[RootCauseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
[Dept_ID] [numeric](10, 0) NOT NULL ,
[RootCauseDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] WITH NOCHECK ADD
CONSTRAINT [PK_DEPT] PRIMARY KEY CLUSTERED
(
[Dept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PART] WITH NOCHECK ADD
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
[Part_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDEPT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDEPT] PRIMARY KEY CLUSTERED
(
[PartDept_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTDETAILLOG] PRIMARY KEY CLUSTERED
(
[PartDetailLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] WITH NOCHECK ADD
CONSTRAINT [PK_PARTINSTRUCT] PRIMARY KEY CLUSTERED
(
[PartInstruct_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PARTMAINLOG] WITH NOCHECK ADD
CONSTRAINT [PK_PARTMAINLOG] PRIMARY KEY CLUSTERED
(
[PartMainLog_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[REJECT] WITH NOCHECK ADD
CONSTRAINT [PK_REJECT] PRIMARY KEY CLUSTERED
(
[Reject_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ROOTCAUSE] WITH NOCHECK ADD
CONSTRAINT [PK_ROOTCAUSE] PRIMARY KEY CLUSTERED
(
[RootCause_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPT] ADD
CONSTRAINT [DF_DEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PART] ADD
CONSTRAINT [DF_PART_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [DF_PARTDEPT_CompletesProcess] DEFAULT (0) FOR [Completes
Process],
CONSTRAINT [DF_PARTDEPT_DateAdded] DEFAULT (getdate()) FOR [DateAdde
d]
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [DF_PARTDETAILLOG_StartTime] DEFAULT (getdate()) FOR [Sta
rtTime]
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [DF_PARTINSTRUCT_DateAdded] DEFAULT (getdate()) FOR [Date
Added],
CONSTRAINT [DF_PARTINSTRUCT_OperShowImage] DEFAULT (1) FOR [OperShow
Image]
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [DF_PARTMAINLOG_DateAdded] DEFAULT (getdate()) FOR [DateA
dded]
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [DF_ROOTCAUSE_RootCauseDate] DEFAULT (getdate()) FOR
[RootCauseDate]
GO
ALTER TABLE [dbo].[PARTDEPT] ADD
CONSTRAINT [FK_PARTDEPT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDEPT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTDETAILLOG] ADD
CONSTRAINT [FK_PARTDETAILLOG_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTDETAILLOG_PARTMAINLOG] FOREIGN KEY
(
[PartMainLog_ID]
) REFERENCES [dbo].[PARTMAINLOG] (
[PartMainLog_ID]
)
GO
ALTER TABLE [dbo].[PARTINSTRUCT] ADD
CONSTRAINT [FK_PARTINSTRUCT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_PARTINSTRUCT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[PARTMAINLOG] ADD
CONSTRAINT [FK_PARTMAINLOG_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
)
GO
ALTER TABLE [dbo].[REJECT] ADD
CONSTRAINT [FK_REJECT_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
),
CONSTRAINT [FK_REJECT_PART] FOREIGN KEY
(
[Part_ID]
) REFERENCES [dbo].[PART] (
[Part_ID]
),
CONSTRAINT [FK_REJECT_ROOTCAUSE] FOREIGN KEY
(
[RootCause_ID]
) REFERENCES [dbo].[ROOTCAUSE] (
[RootCause_ID]
)
GO
ALTER TABLE [dbo].[ROOTCAUSE] ADD
CONSTRAINT [FK_ROOTCAUSE_DEPT] FOREIGN KEY
(
[Dept_ID]
) REFERENCES [dbo].[DEPT] (
[Dept_ID]
)
GO|||DMI wrote:
> Good morning - I have a text file with the information on how to create a
> database, the relationship between tables, etc. I am new to SQL 2005. Wh
at
> I would like to do is to create the database and structure by using this
> following file (MYFILE.SQL) as described below.
> How can I do this?
>
Launch Management Studio, then do File -> Open -> File, navigate to your
MYFILE.SQL, load it, then click Execute

No comments:

Post a Comment