Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 identity_insert

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 31
An 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 57
An 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
Go to Top of Page

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.




Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-08-28 : 14:27:49

Create PROC [dbo].[SP_Trio_Popul_Stg_Tbls] AS

begin
set 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) > 0

INSERT INTO dbo.Name_Pharse_Stg_Tbl3
SELECT
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_SUFF
FROM 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) > 0



cant even create the SP now.


Server: Msg 8101, Level 16, State 1, Procedure SP_Trio_Popul_Stg_Tbls, Line 19
An 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 29
An 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 55
An 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.




Go to Top of Page

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 be
INSERT 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 colid

will 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.
Go to Top of Page
   

- Advertisement -