SQL Server INsert question

Truncate Table sysdba.MDMSACCOUNT_UPLOADSTAGING

SET IDENTITY_INSERT [sysdba].[MDMSACCOUNT_UPLOADSTAGING] ON
go
INSERT INTO [CAST].[sysdba].[MDMSACCOUNT_UPLOADSTAGING]
                  --(--MDMSACCOUNT_UPLOADID,
           (ACCOUNTID,ACCOUNT,SWIFT_BIC_CODE,AUDIT,INSERT_DATETIME,SENT_DATETIME)
SET IDENTITY_INSERT sysdba.MDMSACCOUNT_UPLOADSTAGING OFF


I am trying to insert into a table with Identity Column
SELECT
--(--MDMSACCOUNT_UPLOADID,
([ACCOUNTID],[ACCOUNT],[SWIFT_BIC_CODE],[AUDIT]
      ,CONVERT( varchar(10), INSERT_DATETIME,120) as [INSERT_DATETIME]
      ,[SENT_DATETIME])
FROM [CAST].[sysdba].[MDMSACCOUNT_UPLOAD]
WHERE     AUDIT IN ('A', 'U', 'Y') AND SENT_DATETIME IS NULL
olaayoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you want to "copy" the ID value into the new table, right?
SET IDENTITY_INSERT [sysdba].[MDMSACCOUNT_UPLOADSTAGING] ON

INSERT INTO [CAST].[sysdba].[MDMSACCOUNT_UPLOADSTAGING]
  ( MDMSACCOUNT_UPLOADID
  , ACCOUNTID,ACCOUNT,SWIFT_BIC_CODE,AUDIT,INSERT_DATETIME,SENT_DATETIME
  )
SELECT MDMSACCOUNT_UPLOADID
  , [ACCOUNTID],[ACCOUNT],[SWIFT_BIC_CODE],[AUDIT]
  , CONVERT( varchar(10), INSERT_DATETIME,120) as [INSERT_DATETIME]
  , [SENT_DATETIME])
  FROM [CAST].[sysdba].[MDMSACCOUNT_UPLOAD]
 WHERE     AUDIT IN ('A', 'U', 'Y') 
   AND SENT_DATETIME IS NULL 

SET IDENTITY_INSERT sysdba.MDMSACCOUNT_UPLOADSTAGING OFF

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
if that is not the problem, please clarify
0
 
JoeNuvoCommented:
Is below code is what you are trying to do?
Truncate Table sysdba.MDMSACCOUNT_UPLOADSTAGING

SET IDENTITY_INSERT [sysdba].[MDMSACCOUNT_UPLOADSTAGING] ON
GO
INSERT INTO [CAST].[sysdba].[MDMSACCOUNT_UPLOADSTAGING]
       (ACCOUNTID, ACCOUNT, SWIFT_BIC_CODE, AUDIT,
        INSERT_DATETIME, SENT_DATETIME)
SELECT  ACCOUNTID, ACCOUNT, SWIFT_BIC_CODE, AUDIT,
        CONVERT( varchar(10), INSERT_DATETIME,120) as [INSERT_DATETIME],
        [SENT_DATETIME]
FROM [CAST].[sysdba].[MDMSACCOUNT_UPLOAD]
WHERE     AUDIT IN ('A', 'U', 'Y') AND SENT_DATETIME IS NULL
GO
SET IDENTITY_INSERT sysdba.MDMSACCOUNT_UPLOADSTAGING OFF
GO

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
olaayoAuthor Commented:
I am trying to run  insert in to this table
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [sysdba].[MDMSACCOUNT_UPLOADSTAGING](
      [MDMSACCOUNT_UPLOAD_ID] [int] IDENTITY(1,1) NOT NULL,
      [MDMSACCOUNT_UPLOADID] [char](12) NULL,
      [ACCOUNTID] [char](12) NOT NULL,
      [ACCOUNT] [varchar](30) NOT NULL,
      [SWIFT_BIC_CODE] [varchar](11) NULL,
      [AUDIT] [char](1) NOT NULL,
      [INSERT_DATETIME] [datetime] NOT NULL,
      [SENT_DATETIME] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
0
 
sureshbabukrishCommented:
did not understand your question
0
 
olaayoAuthor Commented:
Truncate Table sysdba.MDMSACCOUNT_UPLOADSTAGING

SET IDENTITY_INSERT [sysdba].[MDMSACCOUNT_UPLOADSTAGING] ON
go
INSERT INTO [CAST].[sysdba].[MDMSACCOUNT_UPLOADSTAGING]
                  --(--MDMSACCOUNT_UPLOADID,
           (ACCOUNTID,ACCOUNT,SWIFT_BIC_CODE,AUDIT,INSERT_DATETIME,SENT_DATETIME)
SET IDENTITY_INSERT sysdba.MDMSACCOUNT_UPLOADSTAGING OFF


I am trying to insert into a table with Identity Column
SELECT
--(--MDMSACCOUNT_UPLOADID,
([ACCOUNTID],[ACCOUNT],[SWIFT_BIC_CODE],[AUDIT]
      ,CONVERT( varchar(10), INSERT_DATETIME,120) as [INSERT_DATETIME]
      ,[SENT_DATETIME])
FROM [CAST].[sysdba].[MDMSACCOUNT_UPLOAD]
WHERE     AUDIT IN ('A', 'U', 'Y') AND SENT_DATETIME IS NULL


I am trying to run  insert in to this table using the query above
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [sysdba].[MDMSACCOUNT_UPLOADSTAGING](
      [MDMSACCOUNT_UPLOAD_ID] [int] IDENTITY(1,1) NOT NULL,
      [MDMSACCOUNT_UPLOADID] [char](12) NULL,
      [ACCOUNTID] [char](12) NOT NULL,
      [ACCOUNT] [varchar](30) NOT NULL,
      [SWIFT_BIC_CODE] [varchar](11) NULL,
      [AUDIT] [char](1) NOT NULL,
      [INSERT_DATETIME] [datetime] NOT NULL,
      [SENT_DATETIME] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
0
 
sureshbabukrishCommented:
what error message are you getting?
0
 
olaayoAuthor Commented:
Perfect solution Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.