Author |
Topic |
gongxia649
So Suave
344 Posts |
Posted - 2006-08-28 : 14:16:17
|
my SP worked on friday, then today i ran it, but it's not working and throws these errors. i googled the error, but i still can't fix it. can you help me?Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 31An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl2' can only be specified when a column list is used and IDENTITY_INSERT is ON.Server: Msg 8101, Level 16, State 1, Procedure USP_Trio_Popul_Stg_Tbls, Line 57An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl3' can only be specified when a column list is used and IDENTITY_INSERT is ON. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 14:24:18
|
Please provide the code that is failing. You are trying to insert an identity value where you shouldn't be.Tara Kizer |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-28 : 14:27:20
|
the code was working on friday.i googled it and it says i have to list the columns explicitly, but there are 57 columns. |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-28 : 14:27:49
|
Create PROC [dbo].[SP_Trio_Popul_Stg_Tbls] ASbeginset nocount on INSERT INTO dbo.Name_Pharse_Stg_Tbl1 SELECT * FROM dbo."00485"-- print @sql UPDATE dbo.Name_Pharse_Stg_Tbl1 SET NM_LAST = SUBSTRING(NM_LAST,1,CHARINDEX(' ',NM_LAST))+',' +SUBSTRING(NM_LAST,CHARINDEX(' ',NM_LAST),LEN(NM_LAST)) WHERE PATINDEX('%,%',NM_LAST) = 0 INSERT INTO dbo.Name_Pharse_Stg_Tbl2 SELECT ID_VOTER ,ID_TOWN ,SUBSTRING(NM_LAST, 1, CHARINDEX(',',NM_LAST)-1) AS NM_LAST ,SUBSTRING(NM_LAST, (CHARINDEX(',',NM_LAST)+2), 100) AS NM_FIRST ,NULL AS NM_MID ,NULL AS NM_SUFF FROM dbo.Name_Pharse_Stg_Tbl1 UPDATE dbo.Name_Pharse_Stg_Tbl2 SET NM_LAST = REPLACE(NM_LAST,',','') WHERE PATINDEX('%,%',NM_LAST) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl2 SET NM_LAST = REPLACE(NM_LAST,'.','') WHERE PATINDEX('%.%',NM_LAST) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl2 SET NM_FIRST = REPLACE(NM_FIRST,',','') WHERE PATINDEX('%,%',NM_FIRST) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl2 SET NM_FIRST = REPLACE(NM_FIRST,'.','') WHERE PATINDEX('%.%',NM_FIRST) > 0INSERT INTO dbo.Name_Pharse_Stg_Tbl3SELECT ID_VOTER,ID_TOWN/* --------------------------------------------------------------------------------------------- * L A S T N A M E C O D E * --------------------------------------------------------------------------------------------- */,CASE/* * WHEN THE LAST NAME DATA LOOKS LIKE "SHAKRA JACOB", CHECK IF THE FIRST WORD IS NOT LIKE JR,SR,II,III,IV,V * AND ASSIGN AS LAST NAME */ WHEN SUBSTRING(NM_LAST, 1, LEN(NM_LAST)) NOT IN ('JR','SR','II','III', 'IV','V') THEN SUBSTRING(NM_LAST, 1, LEN(NM_LAST))END AS NM_LAST/* --------------------------------------------------------------------------------------------- * F I R S T N A M E P H A R S E C O D E * --------------------------------------------------------------------------------------------- */,CASE/* * WHEN THE DATA LOOKS LIKE "SAMANTHA MARIE", CHECK IF THE FIRST WORD IS NOT LIKE JR,SR,II,III,IV,V AND HAVE * THE WORD TO BE AS FIRST NAME i.e., SAMANTHA AS FIRST NAME */ WHEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST)) NOT IN ('JR','SR','II','III', 'IV','V') AND LEN(SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST))) > 1 THEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST))/* * WHEN THE NM_FIRST DATA LOOKS LIKE "SAMANTHA", CHECK IF THE FIRST WORD IS NOT LIKE JR,SR,II,III,IV,V AND HAVE * THE WORD TO BE AS FIRST NAME i.e., SAMANTHA AS FIRST NAME */ WHEN SUBSTRING(NM_FIRST, 1, LEN(NM_FIRST)) NOT IN ('JR','SR','II','III', 'IV','V') AND LEN(SUBSTRING(NM_FIRST, 1, LEN(NM_FIRST))) > 1 AND PATINDEX('% %',NM_FIRST) = 0 THEN SUBSTRING(NM_FIRST, 1, LEN(NM_FIRST))/* * WHEN THE FIRST NAME DATA LOOKS LIKE "JR SAMANTHA", CHECK IF THE FIRST WORD IS LIKE JR,SR,II,III,IV,V * AND GET THE REMAINING DATA AND ASSIGN AS FIRST NAME */ WHEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST)) IN ('JR','SR','II','III', 'IV','V') AND LEN(LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), CHARINDEX(' ',(LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), LEN(NM_FIRST)))))))))) > 2 THEN LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), CHARINDEX(' ',LTRIM(RTRIM(SUBSTRING(NM_FIRST,CHARINDEX(' ',NM_FIRST),LEN(NM_FIRST))))))))/* * WHEN THE FIRST NAME DATA LOOKS LIKE "J SAMANTHA", CHECK IF THE LENGTH OF FIRST WORD = 1 * IGNORE THE FIRST WORD WHICH IS ONE CHARACTER AND GET THE REMAINING DATA AND ASSIGN AS FIRST NAME */ WHEN LEN(SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST))) = 1 THEN LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), LEN(NM_FIRST))))END AS NM_FIRST/*---------------------------------------------------------------------------------------------- * M I D D L E N A M E P H A R S E C O D E *--------------------------------------------------------------------------------------------- */,CASE/* WHEN THE FIRST NAME DATA LOOKS LIKE "M SAMANTHA MARIE", CHECK IF THE FIRST WORD IS OF ONE CHARACTER AND THAT LETTER * AS MIDDLE NAME i.e., M AS MIDDLE NAME */ WHEN LEN(SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST))) = 1 THEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST))/* WHEN THE FIRST NAME DATA LOOKS LIKE "SAMANTHA MARIE", CHECK IF THE FIRST WORD IS NOT OF ONE CHARACTER AND PICK THE LAST WORD * AS MIDDLE NAME, WHICH SHOULD NOT BE LIKE JR i.e., MARIE AS MIDDLE NAME */ WHEN LEN(LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), LEN(NM_FIRST))))) >= 1 AND LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), LEN(NM_FIRST)))) NOT IN ('JR','SR','II','III', 'IV','V') THEN LTRIM(RTRIM(SUBSTRING(NM_FIRST, CHARINDEX(' ',NM_FIRST), LEN(NM_FIRST)))) END AS NM_MID/*---------------------------------------------------------------------------------------------- * S U F F I X P H A R S E C O D E *---------------------------------------------------------------------------------------------- */,CASE/* * WHEN THE FIRST NAME DATA LOOKS LIKE "JR SAMANTHA", CHECK IF THE FIRST WORD IS LIKE JR,SR,II,III,IV,V * AND ASSIGN AS NM_SUFF */ WHEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST)) IN ('JR','SR','II','III', 'IV','V') THEN SUBSTRING(NM_FIRST, 1, CHARINDEX(' ',NM_FIRST)) WHEN RTRIM(LTRIM(REVERSE(SUBSTRING(REVERSE(NM_FIRST), 1, CHARINDEX(' ',REVERSE(NM_FIRST)))))) IN ('JR','SR','II','III', 'IV','V') THEN RTRIM(LTRIM(REVERSE(SUBSTRING(REVERSE(NM_FIRST), 1, CHARINDEX(' ',REVERSE(NM_FIRST)))))) END AS NM_SUFFFROM dbo.Name_Pharse_Stg_Tbl2/* REPLACING JR, SR, II, III, IV, V VALUES WHICH ARE PRESENT IN THE MIDDLE NAME */ UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'JR','') WHERE PATINDEX('%JR%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'(','') WHERE PATINDEX('%(%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,')','') WHERE PATINDEX('%)%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'?','') WHERE PATINDEX('%?%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'SR','') WHERE PATINDEX('%SR%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'II','') WHERE PATINDEX('%II%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'III','') WHERE PATINDEX('%III%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'IV','') WHERE PATINDEX('%IV%',NM_MID) > 0 UPDATE dbo.Name_Pharse_Stg_Tbl3 SET NM_MID = REPLACE(NM_MID,'V','') WHERE PATINDEX('%V%',NM_MID) > 0cant even create the SP now.Server: Msg 8101, Level 16, State 1, Procedure SP_Trio_Popul_Stg_Tbls, Line 19An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl1' can only be specified when a column list is used and IDENTITY_INSERT is ON.Server: Msg 8101, Level 16, State 1, Procedure SP_Trio_Popul_Stg_Tbls, Line 29An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl2' can only be specified when a column list is used and IDENTITY_INSERT is ON.Server: Msg 8101, Level 16, State 1, Procedure SP_Trio_Popul_Stg_Tbls, Line 55An explicit value for the identity column in table 'dbo.Name_Pharse_Stg_Tbl3' can only be specified when a column list is used and IDENTITY_INSERT is ON. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-28 : 14:33:38
|
INSERT INTO dbo.Name_Pharse_Stg_Tbl1 SELECT * FROM dbo."00485"is the statement causing the problem.It should beINSERT INTO dbo.Name_Pharse_Stg_Tbl1 SELECT * FROM dbo.[00485]but that won't solve the problem.select name + ','from syscolumns where id = object_id('Name_Pharse_Stg_Tbl1')order by colidwill give the list of columns for you to copy into the insert statement - just remove the identity and the last comma.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|