SELECT * INTO FAILS

Question

I have following table structure :

CREATE TABLE [dbo].[UTS_USERCLIENT_MAPPING_USER_LIST]
(
    [MAPPING_ID] [int] IDENTITY(1,1) NOT NULL,
    [USER_ID] [varchar](50) NULL,
    [USER_EMAIL_ID] [varchar](100) NULL,
    [USER_CREATED_DATE] [datetime] NULL,
    [USER_IS_ACTIVE] [bit] NULL,

    CONSTRAINT [PK_UTS_USERCLIENT_MAPPING_USER_LIST] 
       PRIMARY KEY CLUSTERED ([MAPPING_ID] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
             ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In stored procedure I have this code:

ALTER PROCEDURE [dbo].[PROC_UTS_USER_CLIENTMAPPING_LIST_SET] 
    (@RETURN_CODE INT OUTPUT,
     @RETURN_MESSAGE NVARCHAR(512) OUTPUT,
     @XML_USER_LIST xml)
AS
BEGIN TRY
    SELECT
        ROW_NUMBER() OVER(ORDER BY x.value('USERNAME[1]','nvarchar(50)')) AS MAPPING_ID,
        x.value('USERNAME[1]', 'nvarchar(50)') as USER_ID,
        x.value('EMAILID[1]', 'nvarchar(50)') as USER_EMAIL_ID,
        x.value('CREATEDDATE[1]', 'datetime') as USER_CREATED_DATE,
        x.value('ISACTIVE[1]', 'bit') as USER_IS_ACTIVE 
    INTO #tempXML
    FROM @XML_USER_LIST.nodes('/DocumentElement/dtLstUsers') AS TEMPTABLE(x)

    SELECT * 
    INTO UTS_USERCLIENT_MAPPING_USER_LIST 
    FROM #tempXML
END TRY

My problem is that above stored procedure is not inserting data into UTS_USERCLIENT_MAPPING_USER_LIST from #tempXML table.

I have ensured that #tempXML table contains values.

Answer 1

There are a few flaws in your query:

1 - you are trying to insert an IDENTITY value without setting IDENTITY_INSERT ON before inserting into your table, and then set it to OFF

SET IDENTITY_INSERT UTS_USERCLIENT_MAPPING_USER_LIST ON 

2 - SELECT * INTO table will assume the table doesn't exist and will try to create it there, will fail -> need to use INSERT INTO SELECT

INSERT INTO UTS_USERCLIENT_MAPPING_USER_LIST (cols)
SELECT cols
FROM  #temp

3 - you are calculating the MAPPING_ID with ROW_NUMBER function which will start from 1 to n (where n is number of nodes you have in xml)every time, but your table has a PRIMARY KEY on MAPPING_ID column which implies is UNIQUE so 2nd time you want to insert MAPPING_ID 1, it will fail.

4 - If you have a CATCH block which is empty, it will hide your errors

Now, the solution without really understanding your needs regarding MAPPING_ID column, is to change the insert statement there to:

INSERT INTO UTS_USERCLIENT_MAPPING_USER_LIST ([USER_ID], [USER_EMAIL_ID], [USER_CREATED_DATE], [USER_IS_ACTIVE])
SELECT  [USER_ID], [USER_EMAIL_ID], [USER_CREATED_DATE], [USER_IS_ACTIVE]
FROM    #tempXML

OR if you have a valid MAPPING_ID found from xml somehow:

SET IDENTITY_INSERT UTS_USERCLIENT_MAPPING_USER_LIST ON

INSERT INTO UTS_USERCLIENT_MAPPING_USER_LIST ([MAPPING_ID], [USER_ID], [USER_EMAIL_ID], [USER_CREATED_DATE], [USER_IS_ACTIVE])
SELECT  [MAPPING_ID], [USER_ID], [USER_EMAIL_ID], [USER_CREATED_DATE], [USER_IS_ACTIVE]
FROM    #tempXML

SET IDENTITY_INSERT UTS_USERCLIENT_MAPPING_USER_LIST OFF