January 30, 2015 at 10:12 am
here is a new scripting tables in my database.
ps: my database is called HEALTHBOARD
USE [HEALTHBOARD]
GO
/****** Object: Table [dbo].[FAMILLE_ARTICLE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAMILLE_ARTICLE](
[CODFAMART] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBFAMART] [varchar](18) NULL,
CONSTRAINT [PK_FAMILLE_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODFAMART] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SITE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SITE](
[CODSITE] [varchar](10) NOT NULL,
[LIBSITE] [varchar](30) NULL,
CONSTRAINT [PK_SITE] PRIMARY KEY CLUSTERED
(
[CODSITE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TYPE_MOUVEMENT] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TYPE_MOUVEMENT](
[CODTYPMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBTYPMVT] [varchar](50) NOT NULL,
CONSTRAINT [PK_TYPE_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODTYPMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DESTINATION] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DESTINATION](
[CODDEST] [varchar](6) NOT NULL,
[LIBDEST] [varchar](20) NOT NULL,
CONSTRAINT [PK_DESTINATION] PRIMARY KEY NONCLUSTERED
(
[CODDEST] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[COMMANDE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COMMANDE](
[CODCMD] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[NUMBONCMD] [numeric](5, 0) NOT NULL,
[LIBCMD] [varchar](30) NOT NULL,
[DATCMD] [date] NOT NULL,
CONSTRAINT [PK_COMMANDE] PRIMARY KEY NONCLUSTERED
(
[CODCMD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[ARTICLE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ARTICLE](
[CODART] [varchar](8) NOT NULL,
[CODFAMART] [numeric](6, 0) NOT NULL,
[DESIGART] [varchar](60) NULL,
[PUBRUTART] [money] NOT NULL,
[PVPUBLIC] [money] NULL,
[SEUILMINI] [numeric](4, 0) NOT NULL,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODART] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[EMPLOYER] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EMPLOYER](
[MATEMP] [numeric](6, 0) NOT NULL,
[NOMEMP] [varchar](10) NULL,
[PRENEMP] [varchar](25) NULL,
[SEXEMP] [char](1) NULL,
[DATNAISSEMP] [date] NULL,
[LIEUNAISSEMP] [varchar](30) NULL,
[TELEMP] [varchar](11) NULL,
[PROFIL] [varchar](15) NULL,
[MDP] [varchar](50) NULL,
[CODSITE] [varchar](10) NULL,
CONSTRAINT [PK_EMPLOYER] PRIMARY KEY NONCLUSTERED
(
[MATEMP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SOUS_SITE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SOUS_SITE](
[CODSOUSIT] [varchar](10) NOT NULL,
[CODSITE] [varchar](10) NOT NULL,
[LIBSOUSIT] [varchar](30) NULL,
CONSTRAINT [PK_SOUS_SITE] PRIMARY KEY CLUSTERED
(
[CODSOUSIT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[MOUVEMENT] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MOUVEMENT](
[CODMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[CODSOUSIT] [varchar](10) NOT NULL,
[CODDEST] [varchar](6) NOT NULL,
[MATEMP] [numeric](6, 0) NOT NULL,
[CODTYPMVT] [numeric](6, 0) NOT NULL,
[DATMVT] [date] NOT NULL,
[CODCONSULT] [varchar](6) NULL,
CONSTRAINT [PK_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[CONTENIR] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CONTENIR](
[CODART] [varchar](8) NOT NULL,
[CODCMD] [numeric](6, 0) NOT NULL,
[QTECMD] [numeric](6, 0) NOT NULL,
CONSTRAINT [PK_CONTENIR] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODCMD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[FAIRE] Script Date: 01/30/2015 17:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAIRE](
[CODART] [varchar](8) NOT NULL,
[CODMVT] [numeric](6, 0) NOT NULL,
[QTEMVT] [numeric](6, 0) NOT NULL,
[DATPEREMP] [date] NOT NULL,
[DATFABRIC] [date] NULL,
[NUMLOT] [numeric](10, 0) NOT NULL,
CONSTRAINT [PK_FAIRE] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_ARTICLE_CONCERNER_FAMILLE_] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[ARTICLE] WITH CHECK ADD CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_] FOREIGN KEY([CODFAMART])
REFERENCES [dbo].[FAMILLE_ARTICLE] ([CODFAMART])
GO
ALTER TABLE [dbo].[ARTICLE] CHECK CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_]
GO
/****** Object: ForeignKey [FK_CONTENIR_CONTENIR_ARTICLE] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE]
GO
/****** Object: ForeignKey [FK_CONTENIR_CONTENIR2_COMMANDE] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE] FOREIGN KEY([CODCMD])
REFERENCES [dbo].[COMMANDE] ([CODCMD])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE]
GO
/****** Object: ForeignKey [FK__EMPLOYER__CODSIT__1CE72E9F] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
/****** Object: ForeignKey [FK__EMPLOYER__CODSIT__1DDB52D8] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
/****** Object: ForeignKey [FK__EMPLOYER__CODSIT__1ECF7711] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
/****** Object: ForeignKey [FK__EMPLOYER__CODSIT__1FC39B4A] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
/****** Object: ForeignKey [FK_FAIRE_FAIRE_ARTICLE] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE]
GO
/****** Object: ForeignKey [FK_FAIRE_FAIRE2_MOUVEMEN] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN] FOREIGN KEY([CODMVT])
REFERENCES [dbo].[MOUVEMENT] ([CODMVT])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN]
GO
/****** Object: ForeignKey [FK_MOUVEMEN_AVOIR_DESTINAT] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT] FOREIGN KEY([CODDEST])
REFERENCES [dbo].[DESTINATION] ([CODDEST])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT]
GO
/****** Object: ForeignKey [FK_MOUVEMEN_ETRE_TYPE_MOU] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU] FOREIGN KEY([CODTYPMVT])
REFERENCES [dbo].[TYPE_MOUVEMENT] ([CODTYPMVT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU]
GO
/****** Object: ForeignKey [FK_MOUVEMEN_EXERCER_EMPLOYER] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER] FOREIGN KEY([MATEMP])
REFERENCES [dbo].[EMPLOYER] ([MATEMP])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER]
GO
/****** Object: ForeignKey [FK_MOUVEMEN_INCLURE_SOUS_SIT] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT] FOREIGN KEY([CODSOUSIT])
REFERENCES [dbo].[SOUS_SITE] ([CODSOUSIT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT]
GO
/****** Object: ForeignKey [FK_SOUS_SIT_RENFERMER_SITE] Script Date: 01/30/2015 17:02:05 ******/
ALTER TABLE [dbo].[SOUS_SITE] WITH CHECK ADD CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE] FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[SOUS_SITE] CHECK CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE]
GO
January 30, 2015 at 10:38 am
Data scripts don't work either:
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ARTICLE_CONCERNER_FAMILLE_". The conflict occurred in database "Sandbox4", table "dbo.FAMILLE_ARTICLE", column 'CODFAMART'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ARTICLE_CONCERNER_FAMILLE_". The conflict occurred in database "Sandbox4", table "dbo.FAMILLE_ARTICLE", column 'CODFAMART'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 109, Level 15, State 1, Line 3
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 2
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 2
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 2
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 2
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 109, Level 15, State 1, Line 2
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FAIRE_FAIRE_ARTICLE". The conflict occurred in database "Sandbox4", table "dbo.ARTICLE", column 'CODART'.
The statement has been terminated.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FAIRE_FAIRE_ARTICLE". The conflict occurred in database "Sandbox4", table "dbo.ARTICLE", column 'CODART'.
The statement has been terminated.
January 30, 2015 at 2:53 pm
sorry I failed to make insertion into table 'FAMILLE_ARTICLE'.
Here the insertion script update and also the conversion of dates
--insert table famille_article
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Médicaments')
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Inflammable')
GO
--insert table article
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0001',1,'Amoxiciline ubi 250mg pdre susp F/60ml',93500,100000,10)
GO
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0002',1,'Amoxico 500mg gel b/100',6025,7025,20)
GO
-- insert table destination
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH1','Bon de pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH2','Bon de pharmacie2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie1','Pharmacie interne F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie2','Pharmacie interne F2')
GO
--insert table site
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF1','Pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF2','Pharmacie2')
go
--insert table sous_site
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP1','PharmaF1','Dispensaire1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP2','PharmaF2','Dispensaire2')
go
--insert table type_mouvement
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F2')
GO
--insert table employer
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(171819,'YAO','kan','M',CONVERT(datetime,'13/06/1984',103),'ferké','01-20-96-63','gestionnaire','cogito23','PharmaF1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(4021,'HUNT','kim','F',CONVERT(datetime,'20/10/1980',103),'bouaké','03-25-89-21','gestionnaire','lynx_123p','PharmaF2')
GO
--insert table mouvement
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'28/01/2015',103),'C00001')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'29/01/2015',103),'C00002')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'30/01/2015',103),'C00003')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'28/01/2015',103),'C00004')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'29/01/2015',103),'C00005')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'30/01/2015',103),'C00006')
GO
--insert table faire
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',1,120,CONVERT(datetime,'12/02/2012',103),CONVERT(datetime,'12/02/2008',103),101023)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',2,80,CONVERT(datetime,'06/12/2014',103),CONVERT(datetime,'06/12/2009',103),119620)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',3,400,CONVERT(datetime,'17/08/2015',103),CONVERT(datetime,'17/08/2010',103),124560)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',4,120,CONVERT(datetime,'25/04/2017',103),CONVERT(datetime,'25/04/2010',103),203645)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',5,80,CONVERT(datetime,'18/12/2020',103),CONVERT(datetime,'18/12/2010',103),308912)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',6,400,CONVERT(datetime,'03/07/2022',103),CONVERT(datetime,'03/07/2011',103),970562)
GO
February 4, 2015 at 4:11 pm
hi,
isn't there anyone to help me
February 5, 2015 at 8:19 am
Let's start from a nice clean slate here since the ddl and sample data posts are all over board. I took the time to reorganize your script so it will actually run on a clean database. I got all the foreign keys in the right order and removed the database name on the inserts. Here is what I came up with which will execute cleanly on an empty database.
CREATE TABLE [dbo].[TYPE_MOUVEMENT](
[CODTYPMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBTYPMVT] [varchar](50) NOT NULL,
CONSTRAINT [PK_TYPE_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODTYPMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SOUS_SITE](
[CODSOUSIT] [varchar](10) NOT NULL,
[CODSITE] [varchar](10) NOT NULL,
[LIBSOUSIT] [varchar](30) NULL,
CONSTRAINT [PK_SOUS_SITE] PRIMARY KEY CLUSTERED
(
[CODSOUSIT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SITE](
[CODSITE] [varchar](10) NOT NULL,
[LIBSITE] [varchar](30) NULL,
CONSTRAINT [PK_SITE] PRIMARY KEY CLUSTERED
(
[CODSITE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MOUVEMENT](
[CODMVT] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[CODSOUSIT] [varchar](10) NOT NULL,
[CODDEST] [varchar](6) NOT NULL,
[MATEMP] [numeric](6, 0) NOT NULL,
[CODTYPMVT] [numeric](6, 0) NOT NULL,
[DATMVT] [date] NOT NULL,
[CODCONSULT] [varchar](6) NULL,
CONSTRAINT [PK_MOUVEMENT] PRIMARY KEY NONCLUSTERED
(
[CODMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FAMILLE_ARTICLE](
[CODFAMART] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[LIBFAMART] [varchar](18) NULL,
CONSTRAINT [PK_FAMILLE_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODFAMART] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FAIRE](
[CODART] [varchar](8) NOT NULL,
[CODMVT] [numeric](6, 0) NOT NULL,
[QTEMVT] [numeric](6, 0) NOT NULL,
[DATPEREMP] [date] NOT NULL,
[DATFABRIC] [date] NULL,
[NUMLOT] [numeric](10, 0) NOT NULL,
CONSTRAINT [PK_FAIRE] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODMVT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EMPLOYER](
[MATEMP] [numeric](6, 0) NOT NULL,
[NOMEMP] [varchar](10) NULL,
[PRENEMP] [varchar](25) NULL,
[SEXEMP] [char](1) NULL,
[DATNAISSEMP] [date] NULL,
[LIEUNAISSEMP] [varchar](30) NULL,
[TELEMP] [varchar](11) NULL,
[PROFIL] [varchar](15) NULL,
[MDP] [varchar](50) NULL,
[CODSITE] [varchar](10) NULL,
CONSTRAINT [PK_EMPLOYER] PRIMARY KEY NONCLUSTERED
(
[MATEMP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[EMPLOYER] WITH CHECK ADD FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
CREATE TABLE [dbo].[DESTINATION](
[CODDEST] [varchar](6) NOT NULL,
[LIBDEST] [varchar](20) NOT NULL,
CONSTRAINT [PK_DESTINATION] PRIMARY KEY NONCLUSTERED
(
[CODDEST] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CONTENIR](
[CODART] [varchar](8) NOT NULL,
[CODCMD] [numeric](6, 0) NOT NULL,
[QTECMD] [numeric](6, 0) NOT NULL,
CONSTRAINT [PK_CONTENIR] PRIMARY KEY CLUSTERED
(
[CODART] ASC,
[CODCMD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[COMMANDE](
[CODCMD] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[NUMBONCMD] [numeric](5, 0) NOT NULL,
[LIBCMD] [varchar](30) NOT NULL,
[DATCMD] [date] NOT NULL,
CONSTRAINT [PK_COMMANDE] PRIMARY KEY NONCLUSTERED
(
[CODCMD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ARTICLE](
[CODART] [varchar](8) NOT NULL,
[CODFAMART] [numeric](6, 0) NOT NULL,
[DESIGART] [varchar](60) NULL,
[PUBRUTART] [money] NOT NULL,
[PVPUBLIC] [money] NULL,
[SEUILMINI] [numeric](4, 0) NOT NULL,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY NONCLUSTERED
(
[CODART] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ARTICLE] WITH CHECK ADD CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_] FOREIGN KEY([CODFAMART])
REFERENCES [dbo].[FAMILLE_ARTICLE] ([CODFAMART])
GO
ALTER TABLE [dbo].[ARTICLE] CHECK CONSTRAINT [FK_ARTICLE_CONCERNER_FAMILLE_]
GO
ALTER TABLE [dbo].[SOUS_SITE] WITH CHECK ADD CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE] FOREIGN KEY([CODSITE])
REFERENCES [dbo].[SITE] ([CODSITE])
GO
ALTER TABLE [dbo].[SOUS_SITE] CHECK CONSTRAINT [FK_SOUS_SIT_RENFERMER_SITE]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT] FOREIGN KEY([CODDEST])
REFERENCES [dbo].[DESTINATION] ([CODDEST])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_AVOIR_DESTINAT]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU] FOREIGN KEY([CODTYPMVT])
REFERENCES [dbo].[TYPE_MOUVEMENT] ([CODTYPMVT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_ETRE_TYPE_MOU]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER] FOREIGN KEY([MATEMP])
REFERENCES [dbo].[EMPLOYER] ([MATEMP])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_EXERCER_EMPLOYER]
GO
ALTER TABLE [dbo].[MOUVEMENT] WITH CHECK ADD CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT] FOREIGN KEY([CODSOUSIT])
REFERENCES [dbo].[SOUS_SITE] ([CODSOUSIT])
GO
ALTER TABLE [dbo].[MOUVEMENT] CHECK CONSTRAINT [FK_MOUVEMEN_INCLURE_SOUS_SIT]
GO
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE_ARTICLE]
GO
ALTER TABLE [dbo].[FAIRE] WITH CHECK ADD CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN] FOREIGN KEY([CODMVT])
REFERENCES [dbo].[MOUVEMENT] ([CODMVT])
GO
ALTER TABLE [dbo].[FAIRE] CHECK CONSTRAINT [FK_FAIRE_FAIRE2_MOUVEMEN]
GO
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE] FOREIGN KEY([CODART])
REFERENCES [dbo].[ARTICLE] ([CODART])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR_ARTICLE]
GO
ALTER TABLE [dbo].[CONTENIR] WITH CHECK ADD CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE] FOREIGN KEY([CODCMD])
REFERENCES [dbo].[COMMANDE] ([CODCMD])
GO
ALTER TABLE [dbo].[CONTENIR] CHECK CONSTRAINT [FK_CONTENIR_CONTENIR2_COMMANDE]
GO
--insert table famille_article
INSERT INTO [FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Médicaments')
GO
INSERT INTO [FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Inflammable')
GO
--insert table article
INSERT INTO [ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0001',1,'Amoxiciline ubi 250mg pdre susp F/60ml',93500,100000,10)
GO
INSERT INTO [ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0002',1,'Amoxico 500mg gel b/100',6025,7025,20)
GO
-- insert table destination
INSERT INTO [DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH1','Bon de pharmacie1')
GO
INSERT INTO [DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH2','Bon de pharmacie2')
GO
INSERT INTO [DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie1','Pharmacie interne F1')
GO
INSERT INTO [DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie2','Pharmacie interne F2')
GO
--insert table site
INSERT INTO [SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF1','Pharmacie1')
GO
INSERT INTO [SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF2','Pharmacie2')
go
--insert table sous_site
INSERT INTO [SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP1','PharmaF1','Dispensaire1')
GO
INSERT INTO [SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP2','PharmaF2','Dispensaire2')
go
--insert table type_mouvement
INSERT INTO [TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite1')
GO
INSERT INTO [TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite2')
GO
INSERT INTO [TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F1')
GO
INSERT INTO [TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F2')
GO
--insert table employer
INSERT INTO [EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(171819,'YAO','kan','M',CONVERT(datetime,'13/06/1984',103),'ferké','01-20-96-63','gestionnaire','cogito23','PharmaF1')
GO
INSERT INTO [EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(4021,'HUNT','kim','F',CONVERT(datetime,'20/10/1980',103),'bouaké','03-25-89-21','gestionnaire','lynx_123p','PharmaF2')
GO
--insert table mouvement
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'28/01/2015',103),'C00001')
GO
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'29/01/2015',103),'C00002')
GO
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'30/01/2015',103),'C00003')
GO
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'28/01/2015',103),'C00004')
GO
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'29/01/2015',103),'C00005')
GO
INSERT INTO [MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'30/01/2015',103),'C00006')
GO
--insert table faire
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',1,120,CONVERT(datetime,'12/02/2012',103),CONVERT(datetime,'12/02/2008',103),101023)
GO
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',2,80,CONVERT(datetime,'06/12/2014',103),CONVERT(datetime,'06/12/2009',103),119620)
GO
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',3,400,CONVERT(datetime,'17/08/2015',103),CONVERT(datetime,'17/08/2010',103),124560)
GO
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',4,120,CONVERT(datetime,'25/04/2017',103),CONVERT(datetime,'25/04/2010',103),203645)
GO
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',5,80,CONVERT(datetime,'18/12/2020',103),CONVERT(datetime,'18/12/2010',103),308912)
GO
INSERT INTO [FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',6,400,CONVERT(datetime,'03/07/2022',103),CONVERT(datetime,'03/07/2011',103),970562)
GO
What is the desired output from this sample data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2015 at 9:59 am
hi sean lange,
thank you for answered and thank you for the time you devoted to my problem.
In fact what I have based on this data sample is:
set up a query that will allow me at a table data output "FAIRE" applied the First Expired First Out system (FEFO). Clearly upon insertion of items out of my table "FAIRE", for example to the item Code (CODART) 'M0001' if I get 400 units the request that I would need to do this:
out of 120 units item(CODART)= 'M0001' and (NUMLOT)=1 (expiry date (DATPEREMP): 12/02/2012)
+ 80 units out of the item (CODART)= 'M0001' and (NUMLOT)=1 (expiration date: (DATPEREMP): 06/12/2014)
+ 400-200 units out of the item (CODART)= 'M0001' and (NUMLOT)=1 (expiry date (DATPEREMP): 08.17.2015).
So if the item 'M001' to which the batch number (NUMLOT) '1' in table 'FAIRE' such a quantity (QTEMVT) = 0 that is to say, its stock = 0 then the request should not consider it as one that expires first
February 5, 2015 at 10:13 am
136romy (2/5/2015)
hi sean lange,thank you for answered and thank you for the time you devoted to my problem.
In fact what I have based on this data sample is:
set up a query that will allow me at a table data output "FAIRE" applied the First Expired First Out system (FEFO). Clearly upon insertion of items out of my table "FAIRE", for example to the item Code (CODART) 'M0001' if I get 400 units the request that I would need to do this:
out of 120 units item(CODART)= 'M0001' and (NUMLOT)=1 (expiry date (DATPEREMP): 12/02/2012)
+ 80 units out of the item (CODART)= 'M0001' and (NUMLOT)=1 (expiration date: (DATPEREMP): 06/12/2014)
+ 400-200 units out of the item (CODART)= 'M0001' and (NUMLOT)=1 (expiry date (DATPEREMP): 08.17.2015).
So if the item 'M001' to which the batch number (NUMLOT) '1' in table 'FAIRE' such a quantity (QTEMVT) = 0 that is to say, its stock = 0 then the request should not consider it as one that expires first
I am not understanding what you need help with here. I understand the concept of FIFO but what are you having trouble with? Is there an insert to a table happening and you want to update something based on the values? Help me understand your problem and I can help you find a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2015 at 10:48 am
yes I do exactly the insertion of 120 units (CODART) = 'M0001' (expiry date (DATPEREMP): 12/02/2012) in table "FAIRE"
then I do the update of 80 units on the item (CODART) = 'M0001' (expiration date: (DATPEREMP): 12/06/2014) in table "FAIRE"
and 400-200 units on the item (CODART) = 'M0001' (expiry date (DATPEREMP): 08.17.2015) in table "FAIRE".
but all these operations are done in the same table "FAIRE" except that it is the quantity (QTEMVT) and batch numbers (NUMLOT) that are different.
February 5, 2015 at 10:52 am
clear for the 400 units requested first I'm an insertion of 120 and 80 Update and 200 in the table "FAIRE"
February 5, 2015 at 10:58 am
136romy (2/5/2015)
yes I do exactly the insertion of 120 units (CODART) = 'M0001' (expiry date (DATPEREMP): 12/02/2012) in table "FAIRE"then I do the update of 80 units on the item (CODART) = 'M0001' (expiration date: (DATPEREMP): 12/06/2014) in table "FAIRE"
and 400-200 units on the item (CODART) = 'M0001' (expiry date (DATPEREMP): 08.17.2015) in table "FAIRE".
but all these operations are done in the same table "FAIRE" except that it is the quantity (QTEMVT) and batch numbers (NUMLOT) that are different.
You are not making this clear. I don't understand what you want to do. When you insert a new row into FAIRE with a CODART of M0001 you want to update the existing rows? How about if you provide a sample insert statement and explain what you want to happen. The biggest challenge at the moment is getting a clear understanding of what you want to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2015 at 11:24 am
yes When i insert a new row into FAIRE with a CODART of M0001 i want to update the existing rows
February 5, 2015 at 11:31 am
Here is a sample insertion in my database
according to this data sample I want to make out 400 units 'M0001' into table FAIRE but starting with the batch(NUMLOT) which expires first
--insert table famille_article
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Médicaments')
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Inflammable')
GO
--insert table article
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0001',1,'Amoxiciline ubi 250mg pdre susp F/60ml',93500,100000,10)
GO
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0002',1,'Amoxico 500mg gel b/100',6025,7025,20)
GO
-- insert table destination
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH1','Bon de pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH2','Bon de pharmacie2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie1','Pharmacie interne F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie2','Pharmacie interne F2')
GO
--insert table site
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF1','Pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF2','Pharmacie2')
go
--insert table sous_site
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP1','PharmaF1','Dispensaire1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP2','PharmaF2','Dispensaire2')
go
--insert table type_mouvement
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F2')
GO
--insert table employer
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(171819,'YAO','kan','M',CONVERT(datetime,'13/06/1984',103),'ferké','01-20-96-63','gestionnaire','cogito23','PharmaF1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(4021,'HUNT','kim','F',CONVERT(datetime,'20/10/1980',103),'bouaké','03-25-89-21','gestionnaire','lynx_123p','PharmaF2')
GO
--insert table mouvement
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'28/01/2015',103),'C00001')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'29/01/2015',103),'C00002')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'30/01/2015',103),'C00003')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'28/01/2015',103),'C00004')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'29/01/2015',103),'C00005')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'30/01/2015',103),'C00006')
GO
--insert table faire
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',1,120,CONVERT(datetime,'12/02/2012',103),CONVERT(datetime,'12/02/2008',103),101023)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',2,80,CONVERT(datetime,'06/12/2014',103),CONVERT(datetime,'06/12/2009',103),119620)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',3,400,CONVERT(datetime,'17/08/2015',103),CONVERT(datetime,'17/08/2010',103),124560)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',4,120,CONVERT(datetime,'25/04/2017',103),CONVERT(datetime,'25/04/2010',103),203645)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',5,80,CONVERT(datetime,'18/12/2020',103),CONVERT(datetime,'18/12/2010',103),308912)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',6,400,CONVERT(datetime,'03/07/2022',103),CONVERT(datetime,'03/07/2011',103),970562)
GO
February 5, 2015 at 11:46 am
For the 400 units that I want out my table "FAIRE" I will first make a inserstion 120 of 'M0001' which the batch number (NUMLOT) = "101023", then I shall have 280 units to go out to reach my 400 units so I make an update of the 80 units of (CODART) = 'M0001' but that the batch number (NUMLOT) = "119620" everything here based on values of the previous insertion. And since my 400 units that I want to get out is not reached then I do an update of 200 units of (CODART) = 'M0001' which the batch number (NUMLOT) = "124560" based on the values of the insertion and the previous update.
February 5, 2015 at 11:54 am
Here is a sample insertion in my database
according to this data sample I want to make out 400 units 'M0001' into table FAIRE but starting with the batch(NUMLOT) which expires first
--insert table famille_article
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Médicaments')
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAMILLE_ARTICLE]
([LIBFAMART])
VALUES
('Inflammable')
GO
--insert table article
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0001',1,'Amoxiciline ubi 250mg pdre susp F/60ml',93500,100000,10)
GO
INSERT INTO [HEALTHBOARD].[dbo].[ARTICLE]
([CODART]
,[CODFAMART]
,[DESIGART]
,[PUBRUTART]
,[PVPUBLIC]
,[SEUILMINI])
VALUES
('M0002',1,'Amoxico 500mg gel b/100',6025,7025,20)
GO
-- insert table destination
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH1','Bon de pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('BPH2','Bon de pharmacie2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie1','Pharmacie interne F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[DESTINATION]
([CODDEST]
,[LIBDEST])
VALUES
('Phcie2','Pharmacie interne F2')
GO
--insert table site
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF1','Pharmacie1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SITE]
([CODSITE]
,[LIBSITE])
VALUES
('PharmaF2','Pharmacie2')
go
--insert table sous_site
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP1','PharmaF1','Dispensaire1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[SOUS_SITE]
([CODSOUSIT]
,[CODSITE]
,[LIBSOUSIT])
VALUES
('DISP2','PharmaF2','Dispensaire2')
go
--insert table type_mouvement
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Sortie gratuite2')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[TYPE_MOUVEMENT]
([LIBTYPMVT])
VALUES
('Entrée pharmacie F2')
GO
--insert table employer
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(171819,'YAO','kan','M',CONVERT(datetime,'13/06/1984',103),'ferké','01-20-96-63','gestionnaire','cogito23','PharmaF1')
GO
INSERT INTO [HEALTHBOARD].[dbo].[EMPLOYER]
([MATEMP]
,[NOMEMP]
,[PRENEMP]
,[SEXEMP]
,[DATNAISSEMP]
,[LIEUNAISSEMP]
,[TELEMP]
,[PROFIL]
,[MDP]
,[CODSITE])
VALUES
(4021,'HUNT','kim','F',CONVERT(datetime,'20/10/1980',103),'bouaké','03-25-89-21','gestionnaire','lynx_123p','PharmaF2')
GO
--insert table mouvement
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'28/01/2015',103),'C00001')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'29/01/2015',103),'C00002')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP1','Phcie1',171819,3,CONVERT(datetime,'30/01/2015',103),'C00003')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'28/01/2015',103),'C00004')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'29/01/2015',103),'C00005')
GO
INSERT INTO [HEALTHBOARD].[dbo].[MOUVEMENT]
([CODSOUSIT]
,[CODDEST]
,[MATEMP]
,[CODTYPMVT]
,[DATMVT]
,[CODCONSULT])
VALUES
('DISP2','Phcie2',4021,4,CONVERT(datetime,'30/01/2015',103),'C00006')
GO
--insert table faire
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',1,120,CONVERT(datetime,'12/02/2012',103),CONVERT(datetime,'12/02/2008',103),101023)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',2,80,CONVERT(datetime,'06/12/2014',103),CONVERT(datetime,'06/12/2009',103),119620)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0001',3,400,CONVERT(datetime,'17/08/2015',103),CONVERT(datetime,'17/08/2010',103),124560)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',4,120,CONVERT(datetime,'25/04/2017',103),CONVERT(datetime,'25/04/2010',103),203645)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',5,80,CONVERT(datetime,'18/12/2020',103),CONVERT(datetime,'18/12/2010',103),308912)
GO
INSERT INTO [HEALTHBOARD].[dbo].[FAIRE]
([CODART]
,[CODMVT]
,[QTEMVT]
,[DATPEREMP]
,[DATFABRIC]
,[NUMLOT])
VALUES
('M0002',6,400,CONVERT(datetime,'03/07/2022',103),CONVERT(datetime,'03/07/2011',103),970562)
GO
February 5, 2015 at 12:20 pm
yes When i insert a new row into FAIRE with a CODART of M0001 i want to update the existing rows
What does that mean? I think the biggest challenge you have is that your data structures are not normalized and you are fighting a poor design.
Slow down a bit here. You keep tossing mountains of sample data at this. What I don't understand is the process. You have provided plenty of data already.
If your table data matches the sample I posted earlier you should three rows in FAIRE where CODART = 'M0001'. Now, explain to me what the process it. Don't just start saying put data here and move data there. What happens in the system at this point that you are trying to deal with. Is somebody inserting a new row to that table with the same CODART? I think that is what is happening. What are the values for that insert and what should the existing 3 rows look when the insert is complete?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply