March 4, 2016 at 3:40 pm
Hello,
I have two existing tables: Table 1 and Table2.
Both tables have some data.
So I want to copy some data from one table to another not all and if both data are similar in both tables then I just want to replace that data not copy. I want exact output not more than that.
In Table 1, I have 20373 rows and In Table 2, I have 3545 rows.
So I just want to copy that rows which are not included in Table2.
Does it possible to copy some data from one table to another?How?
Now I am giving you my live example:
We are using Jenzabar ERP. In that front end side in advising tab we couldn't populate data. And after some research I found that advising tab is depend on student division tab. That means when someone fill advisor information in student division tab it will automatically reflect to advising tab but for some reason it's not working. So I just want to insert data from backend means SQL server side.
Advisor_stud_table is refers to advising tab and student_div_mast table is refers to student division tab.
In student_div_mast , I have 20373 data with student id and advisor id.
And In Advisor_stud_table , I have 3545 data with student id and advisor id.
So I wrote following query and I get an error message.
insert into advisor_stud_table(advisor_id) select advisor_id_num from student_div_mast.id_num=advisor_stud_table.id_num and student_div_mast.advisor_id_num is not null;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Does any one know about appropriate query to copy only specific data from one table to another not all? And Student id is common key between two tables.
I am using this query:
insert ADVISOR_STUD_TABLE(ID_NUM,ADVISOR_ID) select s1.ID_NUM,s1.ADVISOR_ID_NUM from STUDENT_DIV_MAST s1
where s1.ADVISOR_ID_NUM is not null and
not exists(select 1 from ADVISOR_STUD_TABLE a1 where a1.ADVISOR_ID=s1.ADVISOR_ID_NUM and a1.ID_NUM=s1.ID_NUM);
Msg 515, Level 16, State 2, Line 10
Cannot insert the value NULL into column 'SEQ_NUM', table 'TmsEPly.dbo.ADVISOR_STUD_TABLE'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Thanks
March 4, 2016 at 7:12 pm
RV16 (3/4/2016)
Hello,I have two existing tables: Table 1 and Table2.
Both tables have some data.
So I want to copy some data from one table to another not all and if both data are similar in both tables then I just want to replace that data not copy. I want exact output not more than that.
In Table 1, I have 20373 rows and In Table 2, I have 3545 rows.
So I just want to copy that rows which are not included in Table2.
Does it possible to copy some data from one table to another?How?
Now I am giving you my live example:
We are using Jenzabar ERP. In that front end side in advising tab we couldn't populate data. And after some research I found that advising tab is depend on student division tab. That means when someone fill advisor information in student division tab it will automatically reflect to advising tab but for some reason it's not working. So I just want to insert data from backend means SQL server side.
Advisor_stud_table is refers to advising tab and student_div_mast table is refers to student division tab.
In student_div_mast , I have 20373 data with student id and advisor id.
And In Advisor_stud_table , I have 3545 data with student id and advisor id.
So I wrote following query and I get an error message.
insert into advisor_stud_table(advisor_id) select advisor_id_num from student_div_mast.id_num=advisor_stud_table.id_num and student_div_mast.advisor_id_num is not null;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Does any one know about appropriate query to copy only specific data from one table to another not all? And Student id is common key between two tables.
I am using this query:
insert ADVISOR_STUD_TABLE(ID_NUM,ADVISOR_ID) select s1.ID_NUM,s1.ADVISOR_ID_NUM from STUDENT_DIV_MAST s1
where s1.ADVISOR_ID_NUM is not null and
not exists(select 1 from ADVISOR_STUD_TABLE a1 where a1.ADVISOR_ID=s1.ADVISOR_ID_NUM and a1.ID_NUM=s1.ID_NUM);
Msg 515, Level 16, State 2, Line 10
Cannot insert the value NULL into column 'SEQ_NUM', table 'TmsEPly.dbo.ADVISOR_STUD_TABLE'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Thanks
Is this similar to the similar question at http://www.sqlservercentral.com/Forums/Topic1766845-3077-1.aspx?
March 7, 2016 at 8:01 am
No, I just give same title by mistake but I want to insert some data not update. I am now on next step of my phase.
Thanks
March 7, 2016 at 8:19 am
RV16 (3/7/2016)
No, I just give same title by mistake but I want to insert some data not update. I am now on next step of my phase.Thanks
Different question, but similar answer: do not try to insert NULLs to a non-nullable column.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 7, 2016 at 8:24 am
OK. I just want to copy one table's data to another table without null values and I already wrote query for that...
insert ADVISOR_STUD_TABLE(ID_NUM,ADVISOR_ID) select s1.ID_NUM,s1.ADVISOR_ID_NUM from STUDENT_DIV_MAST s1
where s1.ADVISOR_ID_NUM is not null and
not exists(select 1 from ADVISOR_STUD_TABLE a1 where a1.ADVISOR_ID=s1.ADVISOR_ID_NUM and a1.ID_NUM=s1.ID_NUM);
I already mentioned in that query that I don't need null values and still I get an error message...
Thanks
March 7, 2016 at 8:33 am
The error message mentions SEQ_NUM. Put something in that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 7, 2016 at 8:55 am
I don't have SEQ_NUM on the master table.
This one is the table from which I want to copy data
USE [TmsEPrd]
GO
/****** Object: Table [dbo].[STUDENT_DIV_MAST] Script Date: 3/7/2016 8:54:45 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[STUDENT_DIV_MAST](
[APPID] [int] NOT NULL,
[ID_NUM] [int] NOT NULL,
[DIV_CDE] [char](2) NOT NULL,
[CLASS_CDE] [char](2) NULL,
[ADVISOR_ID_NUM] [int] NULL,
[NUM_TRANSCR_COPIES] [int] NOT NULL,
[CLASS_RANK] [char](5) NULL,
[CLASS_RANK_HI_RANG] [char](5) NULL,
[CLASS_SIZE] [char](5) NULL,
[CERTIFICATION] [char](5) NULL,
[GRAD_HONORS_1] [char](5) NULL,
[GRAD_HONORS_2] [char](5) NULL,
[GRAD_HONORS_3] [char](5) NULL,
[GRAD_HONORS_4] [char](5) NULL,
[EXPECTED_GRAD_YR] [char](4) NULL,
[EXPECTED_GRAD_TRM] [char](2) NULL,
[ENTRY_DTE] [datetime] NULL,
[EXIT_DTE] [datetime] NULL,
[EXIT_REASON] [char](2) NULL,
[RE_ENTRY_DTE] [datetime] NULL,
[NUM_LEAVE_OF_ABS] [int] NOT NULL,
[LAST_LEAVE_OF_ABS] [datetime] NULL,
[LAST_YR_ACAD_UPD] [char](4) NULL,
[LAST_TRM_ACAD_UPD] [char](2) NULL,
[LAST_ACADEMIC_UPD] [datetime] NULL,
[LAST_GRADE_RPT] [datetime] NULL,
[LAST_YR_REG] [char](4) NULL,
[LAST_TRM_REG] [char](2) NULL,
[XFER_HRS_ATTEMPTED] [numeric](5, 2) NOT NULL,
[XFER_RS_EARNED] [numeric](5, 2) NOT NULL,
[XFER_HRS_GPA] [numeric](5, 2) NOT NULL,
[XFER_QUAL_PTS] [numeric](7, 2) NOT NULL,
[XFER_GPA] [numeric](7, 4) NOT NULL,
[LOCAL_HRS_ATTEMPT] [numeric](5, 2) NOT NULL,
[LOCAL_HRS_EARNED] [numeric](5, 2) NOT NULL,
[LOCAL_HRS_GPA] [numeric](5, 2) NOT NULL,
[LOCAL_QUAL_PTS] [numeric](7, 2) NOT NULL,
[LOCAL_GPA] [numeric](7, 4) NOT NULL,
[CAREER_HRS_ATTEMPT] [numeric](5, 2) NOT NULL,
[CAREER_HRS_EARNED] [numeric](5, 2) NOT NULL,
[CAREER_HRS_GPA] [numeric](5, 2) NOT NULL,
[CAREER_QUAL_PTS] [numeric](7, 2) NOT NULL,
[CAREER_GPA] [numeric](7, 4) NOT NULL,
[TRM_HRS_ATTEMPT] [numeric](5, 2) NOT NULL,
[TRM_HRS_EARNED] [numeric](5, 2) NOT NULL,
[TRM_HRS_GPA] [numeric](5, 2) NOT NULL,
[TRM_QUAL_PTS] [numeric](7, 2) NOT NULL,
[TRM_GPA] [numeric](7, 4) NOT NULL,
[WRK_HRS_ATTEMPT_1] [numeric](5, 2) NOT NULL,
[WRK_HRS_EARNED_1] [numeric](5, 2) NOT NULL,
[WRK_HRS_GPA_1] [numeric](5, 2) NOT NULL,
[WRK_QUAL_PTS_1] [numeric](7, 2) NOT NULL,
[WRK_GPA_1] [numeric](7, 4) NOT NULL,
[WRK_HRS_ATTEMPT_2] [numeric](5, 2) NOT NULL,
[WRK_HRS_EARNED_2] [numeric](5, 2) NOT NULL,
[WRK_HRS_GPA_2] [numeric](5, 2) NOT NULL,
[WRK_QUAL_PTS_2] [numeric](7, 2) NOT NULL,
[WRK_GPA_2] [numeric](7, 4) NOT NULL,
[CAREER_CRTYPE1_HRS] [numeric](5, 2) NOT NULL,
[CAREER_CRTYPE2_HRS] [numeric](5, 2) NOT NULL,
[CAREER_CRTYPE3_HRS] [numeric](5, 2) NOT NULL,
[TRM_CRTYPE1_HRS] [numeric](5, 2) NOT NULL,
[TRM_CRTYPE2_HRS] [numeric](5, 2) NOT NULL,
[TRM_CRTYPE3_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_1_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_2_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_3_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_4_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_5_HRS] [numeric](5, 2) NOT NULL,
[CAREER_GRADE_6_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_1_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_2_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_3_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_4_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_5_HRS] [numeric](5, 2) NOT NULL,
[TRM_GRADE_6_HRS] [numeric](5, 2) NOT NULL,
[ADVISOR_ID_NUM_2] [int] NULL,
[ADVISOR_ID_NUM_3] [int] NULL,
[TRANSFER_IN] [char](1) NOT NULL,
[FIRST_TIME_POST_SECONDARY] [char](1) NOT NULL,
[ADV_GRP_CDE] [varchar](15) NULL,
[FT_TR_YEAR_DEF_APPID] [int] NULL,
[FT_TR_TERM_DEF_APPID] [int] NULL,
[COHORT_DEFINITION_APPID] [int] NULL,
[UDEF_1A_1] [char](1) NULL,
[UDEF_1A_2] [char](1) NULL,
[UDEF_1A_3] [char](1) NULL,
[UDEF_1A_4] [char](1) NULL,
[UDEF_1A_5] [char](1) NULL,
[UDEF_2A_1] [char](2) NULL,
[UDEF_2A_2] [char](2) NULL,
[UDEF_2A_3] [char](2) NULL,
[UDEF_3A_1] [char](3) NULL,
[UDEF_3A_2] [char](3) NULL,
[UDEF_3A_3] [char](3) NULL,
[UDEF_5A_1] [char](5) NULL,
[UDEF_5A_2] [char](5) NULL,
[UDEF_HRS_GPA] [numeric](5, 2) NOT NULL,
[UDEF_QUAL_PTS] [numeric](7, 2) NOT NULL,
[UDEF_GPA] [numeric](7, 4) NOT NULL,
[UDEF_DTE_1] [datetime] NULL,
[UDEF_DTE_2] [datetime] NULL,
[UDEF_DTE_3] [datetime] NULL,
[DEF_DTE_4] [datetime] NULL,
[UDEF_3_2_1] [numeric](3, 2) NOT NULL,
[UDEF_3_2_2] [numeric](3, 2) NOT NULL,
[UDEF_5_2_1] [numeric](5, 2) NOT NULL,
[UDEF_5_2_2] [numeric](5, 2) NOT NULL,
[UDEF_5_2_3] [numeric](5, 2) NOT NULL,
[UDEF_5_2_4] [numeric](5, 2) NOT NULL,
[UDEF_ID_NUM] [int] NULL,
[APPROWVERSION] [timestamp] NOT NULL,
[USER_NAME] [varchar](513) NULL,
[JOB_NAME] [varchar](30) NULL,
[JOB_TIME] [datetime] NULL,
CONSTRAINT [PK_STUDENT_DIV_MAST] PRIMARY KEY CLUSTERED
(
[APPID] 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
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT (NEXT VALUE FOR [STUDENT_DIV_MAST_SEQ]) FOR [APPID]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [NUM_TRANSCR_COPIES]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [NUM_LEAVE_OF_ABS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [XFER_HRS_ATTEMPTED]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [XFER_RS_EARNED]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [XFER_HRS_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [XFER_QUAL_PTS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [XFER_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [LOCAL_HRS_ATTEMPT]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [LOCAL_HRS_EARNED]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [LOCAL_HRS_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [LOCAL_QUAL_PTS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [LOCAL_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_HRS_ATTEMPT]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_HRS_EARNED]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_HRS_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_QUAL_PTS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_HRS_ATTEMPT]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_HRS_EARNED]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_HRS_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_QUAL_PTS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_ATTEMPT_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_EARNED_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_GPA_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_QUAL_PTS_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_GPA_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_ATTEMPT_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_EARNED_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_HRS_GPA_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_QUAL_PTS_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [WRK_GPA_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_CRTYPE1_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_CRTYPE2_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_CRTYPE3_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_CRTYPE1_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_CRTYPE2_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_CRTYPE3_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_1_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_2_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_3_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_4_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_5_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [CAREER_GRADE_6_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_1_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_2_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_3_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_4_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_5_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [TRM_GRADE_6_HRS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ('N') FOR [TRANSFER_IN]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ('N') FOR [FIRST_TIME_POST_SECONDARY]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_HRS_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_QUAL_PTS]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_GPA]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_3_2_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_3_2_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_5_2_1]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_5_2_2]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_5_2_3]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] ADD DEFAULT ((0)) FOR [UDEF_5_2_4]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_111905] FOREIGN KEY([CERTIFICATION])
REFERENCES [dbo].[CERTIFICATION_DEF] ([CERT_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_111905]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_111929] FOREIGN KEY([GRAD_HONORS_1])
REFERENCES [dbo].[HONOR_DEFINITION] ([HONOR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_111929]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_111933] FOREIGN KEY([GRAD_HONORS_3])
REFERENCES [dbo].[HONOR_DEFINITION] ([HONOR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_111933]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_111936] FOREIGN KEY([GRAD_HONORS_4])
REFERENCES [dbo].[HONOR_DEFINITION] ([HONOR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_111936]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_111939] FOREIGN KEY([GRAD_HONORS_2])
REFERENCES [dbo].[HONOR_DEFINITION] ([HONOR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_111939]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_20150605002] FOREIGN KEY([FT_TR_YEAR_DEF_APPID])
REFERENCES [dbo].[YEAR_DEF] ([APPID])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_20150605002]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_20150605003] FOREIGN KEY([FT_TR_TERM_DEF_APPID])
REFERENCES [dbo].[TERM_DEF] ([APPID])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_20150605003]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_20150605004] FOREIGN KEY([COHORT_DEFINITION_APPID])
REFERENCES [dbo].[COHORT_DEFINITION] ([APPID])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_20150605004]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3320] FOREIGN KEY([CLASS_CDE])
REFERENCES [dbo].[CLASS_DEFINITION] ([CLASS_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3320]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3329] FOREIGN KEY([EXPECTED_GRAD_TRM])
REFERENCES [dbo].[TERM_DEF] ([TRM_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3329]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3332] FOREIGN KEY([LAST_TRM_ACAD_UPD])
REFERENCES [dbo].[TERM_DEF] ([TRM_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3332]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3335] FOREIGN KEY([LAST_TRM_REG])
REFERENCES [dbo].[TERM_DEF] ([TRM_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3335]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3338] FOREIGN KEY([EXPECTED_GRAD_YR])
REFERENCES [dbo].[YEAR_DEF] ([YR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3338]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3341] FOREIGN KEY([LAST_YR_ACAD_UPD])
REFERENCES [dbo].[YEAR_DEF] ([YR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3341]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3344] FOREIGN KEY([LAST_YR_REG])
REFERENCES [dbo].[YEAR_DEF] ([YR_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3344]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_3507] FOREIGN KEY([ADV_GRP_CDE])
REFERENCES [dbo].[ADVISING_GROUP_DEF] ([ADV_GRP_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_3507]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_98545] FOREIGN KEY([ID_NUM])
REFERENCES [dbo].[STUDENT_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_98545]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_98548] FOREIGN KEY([DIV_CDE])
REFERENCES [dbo].[DIVISION_DEF] ([DIV_CDE])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_98548]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_98602] FOREIGN KEY([UDEF_ID_NUM])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_98602]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_99246] FOREIGN KEY([ADVISOR_ID_NUM])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_99246]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_99251] FOREIGN KEY([ADVISOR_ID_NUM_3])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_99251]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [RE_REF_99254] FOREIGN KEY([ADVISOR_ID_NUM_2])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [RE_REF_99254]
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] WITH CHECK ADD CONSTRAINT [CKC_USER_NAME_STUDENT_DIV_MAST] CHECK ((len([USER_NAME])<(16)))
GO
ALTER TABLE [dbo].[STUDENT_DIV_MAST] CHECK CONSTRAINT [CKC_USER_NAME_STUDENT_DIV_MAST]
GO
This one is table which I want to copy data
USE [TmsEPrd]
GO
/****** Object: Table [dbo].[ADVISOR_STUD_TABLE] Script Date: 3/7/2016 8:52:06 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[ADVISOR_STUD_TABLE](
[APPID] [int] NOT NULL,
[ADVISOR_ID] [int] NOT NULL,
[ID_NUM] [int] NOT NULL,
[DIV_CDE] [char](2) NULL,
[SEQ_NUM] [int] NOT NULL,
[ADVISOR_NUM] [int] NOT NULL,
[ADVISOR_TYPE] [char](5) NULL,
[ADVISOR_TABLE_STS] [char](1) NOT NULL,
[SESS_CDE] [char](8) NULL,
[YR_CDE] [char](4) NULL,
[TRM_CDE] [char](2) NULL,
[SECURITY_CLASS_1] [char](8) NULL,
[SECURITY_CLASS_2] [char](8) NULL,
[SECURITY_CLASS_3] [char](8) NULL,
[COMMENT_TXT] [char](30) NULL,
[BEGIN_DTE] [datetime] NULL,
[END_DTE] [datetime] NULL,
[ADV_GRP_CDE] [varchar](15) NULL,
[APPROWVERSION] [timestamp] NOT NULL,
[USER_NAME] [varchar](513) NULL,
[JOB_NAME] [varchar](30) NULL,
[JOB_TIME] [datetime] NULL,
CONSTRAINT [PK_ADVISOR_STUD_TABLE] PRIMARY KEY CLUSTERED
(
[ADVISOR_ID] ASC,
[ID_NUM] ASC,
[SEQ_NUM] 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
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] ADD DEFAULT (NEXT VALUE FOR [ADVISOR_STUD_TABLE_SEQ]) FOR [APPID]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] ADD DEFAULT ((0)) FOR [ADVISOR_NUM]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] ADD DEFAULT ('A') FOR [ADVISOR_TABLE_STS]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_101] FOREIGN KEY([ADV_GRP_CDE])
REFERENCES [dbo].[ADVISING_GROUP_DEF] ([ADV_GRP_CDE])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_101]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_2455] FOREIGN KEY([ADVISOR_ID])
REFERENCES [dbo].[ADVISOR_MASTER] ([ADVISOR_ID])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_2455]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_2458] FOREIGN KEY([ID_NUM])
REFERENCES [dbo].[ADV_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_2458]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_2495] FOREIGN KEY([ADVISOR_TYPE])
REFERENCES [dbo].[ADVISOR_TYPE_DEF] ([ADVISOR_TYPE])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_2495]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_3480] FOREIGN KEY([TRM_CDE])
REFERENCES [dbo].[TERM_DEF] ([TRM_CDE])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_3480]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_3483] FOREIGN KEY([YR_CDE])
REFERENCES [dbo].[YEAR_DEF] ([YR_CDE])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_3483]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [AV_REF_7788] FOREIGN KEY([DIV_CDE])
REFERENCES [dbo].[DIVISION_DEF] ([DIV_CDE])
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [AV_REF_7788]
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] WITH CHECK ADD CONSTRAINT [CKC_USER_NAME_ADVISOR_STUD_TABLE] CHECK ((len([USER_NAME])<(16)))
GO
ALTER TABLE [dbo].[ADVISOR_STUD_TABLE] CHECK CONSTRAINT [CKC_USER_NAME_ADVISOR_STUD_TABLE]
GO
March 9, 2016 at 8:03 am
I would just like to add, that if you are inserting data into a table that is owned and manged by the software vendor without going through their application you are almost certainly going to invalidate any support contract that you have with them and this could be a very costly error. If you can't populate the data through the application there is probably a good reason for it - audit and traceability, dependent tables you are not aware of, triggers etc.
DON'T DO IT!
If it is open source software then no problem, but again expect a hefty support bill when it causes errors elswhere in your system
March 9, 2016 at 9:27 am
Thanks for your suggestion. I will follow your suggestion.
March 14, 2016 at 3:07 pm
aaron.reese (3/9/2016)
I would just like to add, that if you are inserting data into a table that is owned and manged by the software vendor without going through their application you are almost certainly going to invalidate any support contract that you have with them and this could be a very costly error. If you can't populate the data through the application there is probably a good reason for it - audit and traceability, dependent tables you are not aware of, triggers etc.DON'T DO IT!
If it is open source software then no problem, but again expect a hefty support bill when it causes errors elswhere in your system
Doubling down on what Aaron is saying here -- VERY risky modifying purchased and supported code! Talk to your ERP manager and have them contact the vendor: it could be an error in their code, or there could be a procedural/training error on your side that the system is not being used properly.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply