Insert query Sql-CodePudding

I need some assistance with syntax issue for the following query. I need to create an Insert query and format some of the fields: (User_RecID, Create_date, Create_Time, Update_date, Update_Time)

I need to set the User_RecordId to an auto incremental ID, and also format a varchar value of the created_date, & update_date to break out the date and time into separate fields.

Also receiving the following error msg:

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

**NOTE: The date fields are actually varchar(250).Still need help with the date fields since the are strings needing conversion to Date & time fields. How do I incorporate the Userid code into the insert statement?

enter code here
   INSERT INTO [dbo].[dw.tblUsers]
           ([User_RecID] --Autogenerated ID
           ,[User_ID]
           ,[FirstName]
           ,[LastName]
           ,[FullName]
           ,[EMail]
           ,[UserRoles]
           ,[PostionType]
           ,[ManagerID]
           ,[UUID]
           ,[External_UUID]
           ,[home_Location_id]
           ,[Home_Organization_ID]
           ,[Record_types]
           ,[Location_Ceiling_ID]
           ,[Organization_Ceiling_ID]
           ,[Payroll_Identifier])
           --,[Created_Date]
           --,[Created_Time]
          -- ,[Update_Date]
           --,[Update_Time])
   Select   ROW_NUMBER() OVER (PARTITION BY User_RecID ORDER BY User_RecID) AS User_RecID
            ,id-- User_ID
            ,first_name 
            ,last_name 
            ,full_name 
            ,email 
            ,role_id --UserRoles
            ,position --PositionType
            ,manager_id 
            ,uuid 
            ,external_uuid 
            ,home_location_id 
            ,home_organization_id 
            ,[type] --Record_Types
            ,location_ceiling_id 
            ,organization_ceiling_id 
            ,payroll_identifier 
            --, Format(CONVERT(DATE, created_at, 103), "MM/DD/YYYY") --Create Date
            --, Format(CONVERT(Time, created_at, 120), "hh:mm:ss") --Create Time
            --, Format(CONVERT(DATE, updated_at, 103), "MM/DD/YYYY") --Update Date
            --, Format(CONVERT(Time, updated_at, 120), "hh:mm:ss") --Update Time
            from stg.users

CodePudding user response：

modify User_RecID to be an Identity. Add a Primary key index to it. Turn of Identity_Insert while loading the data and activate it after. Make sure to backup your data before making changes. afterward, you don't need User_RecID during an insert because it will autoincrement

Alter Table dw.tblUsers
alter column User_RecID Int Identity(1, 1) Not null

SET IDENTITY_INSERT dw.tblUsers OFF

... insert your data

SET IDENTITY_INSERT dw.tblUsers ON

CodePudding user response：

Create an identity(1,1) As @Golden pointed out.

To insert datetime as a varchar:

select convert(varchar(20), GETDATE(), 120)
-- 2021-11-18

select right(convert(varchar(16), GETDATE(), 120),5)
-- 21:20

To insert [Userid] you can try a subquery:

insert into [dbo].[dw.tblUsers]
...
select (select User_ID from someWhere s where s.id = u.id)
       ,first_name 
       ,last_name 
from stg.users u
...