May 9, 2018 at 11:56 am
Hello
I want to copy records from another database table to my Employee table i am using below quey to insert records. I have 4k records to insert to destination table but m EMPKEY is giving "cannot insert duplicate record " since my EMPKEY is unique
insert into [SEC].[dbo].[EMPREC]
( [ID] ,[EMPID],[TYPE] ,[],[ACTIVATE],[DEACTIVATE] ,[STATUS] [EMPKEY]
select ID AS ID,a.SNO AS EMPID,'1' AS TYPE,a.StartDate AS ACTIVATE,a.ExpireDate AS DEACTIVATE,'1' AS STATUS '1001' AS EMPKEY
from SCS.DBO.EMPLEVEL a
The above query can only insert single record and how to make it insert all record making each EMPKEY unique and i want to start from 1001 and it should insert new empkey for each insert.
How to do it ?
Regards
May 9, 2018 at 12:04 pm
aloshya - Wednesday, May 9, 2018 11:56 AMHello
I want to copy records from another database table to my Employee table i am using below quey to insert records. I have 4k records to insert to destination table but m EMPKEY is giving "cannot insert duplicate record " since my EMPKEY is unique
insert into [SEC].[dbo].[EMPREC]
( [ID] ,[EMPID],[TYPE] ,[],[ACTIVATE],[DEACTIVATE] ,[STATUS] [EMPKEY]
select ID AS ID,a.SNO AS EMPID,'1' AS TYPE,a.StartDate AS ACTIVATE,a.ExpireDate AS DEACTIVATE,'1' AS STATUS '1001' AS EMPKEY
from SCS.DBO.EMPLEVEL aThe above query can only insert single record and how to make it insert all record making each EMPKEY unique and i want to start from 1001 and it should insert new empkey for each insert.
How to do it ?
Regards
First, the code as posted as syntax issues.
Second, without the DDL for at least the target table, can't really help. What is the data type of EMPKEY and is it an identity column?
May 9, 2018 at 12:12 pm
Something like this, maybe:WITH SourceData
AS
(
SELECT
ID = ID
, EMPID = a.SNO
, TYPE = '1'
, ACTIVATE = a.StartDate
, DEACTIVATE = a.ExpireDate
, STATUS = '1'
, EMPKEY = ROW_NUMBER() OVER (ORDER BY
(
SELECT NULL
)
) + 1000
FROM SCS.DBO.EMPLEVEL a
)
INSERT SEC.dbo.EMPREC
(
ID
, EMPID
, TYPE
, ACTIVATE
, DEACTIVATE
, STATUS
, EMPKEY
)
SELECT
SourceData.ID
, SourceData.EMPID
, SourceData.TYPE
, SourceData.ACTIVATE
, SourceData.DEACTIVATE
, SourceData.STATUS
, SourceData.EMPKEY
FROM SourceData;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 9, 2018 at 12:18 pm
Lynn Pettis - Wednesday, May 9, 2018 12:04 PMaloshya - Wednesday, May 9, 2018 11:56 AMHello
I want to copy records from another database table to my Employee table i am using below quey to insert records. I have 4k records to insert to destination table but m EMPKEY is giving "cannot insert duplicate record " since my EMPKEY is unique
insert into [SEC].[dbo].[EMPREC]
( [ID] ,[EMPID],[TYPE] ,[ACTIVATE],[DEACTIVATE] ,[STATUS] [EMPKEY]
select ID AS ID,a.SNO AS EMPID,'1' AS TYPE,a.StartDate AS ACTIVATE,a.ExpireDate AS DEACTIVATE,'1' AS STATUS '1001' AS EMPKEY
from SCS.DBO.EMPLEVEL aThe above query can only insert single record and how to make it insert all record making each EMPKEY unique and i want to start from 1001 and it should insert new empkey for each insert.
How to do it ?
Regards
First, the code as posted as syntax issues.
Second, without the DDL for at least the target table, can't really help. What is the data type of EMPKEY and is it an identity column?
Destination Table
CREATE TABLE [dbo].[EMPREC](
[ID] [bigint] NOT NULL, [EMPID] [int] NOT NULL,[TYPE] [int] NOT NULL,[ACTIVATE] [datetime] NOT NULL,[DEACTIVATE] [datetime] NOT NULL,
[STATUS] [int] NOT NULL, [EMPKEY] [int] NOT NULL
CONSTRAINT [EMPREC_EMPKEY_PK] PRIMARY KEY NONCLUSTERED
(
[EMPKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Source Table
CREATE TABLE [dbo].[emplevel](
[Id] [int] IDENTITY(1,1) NOT NULL,
[sno] [int] NOT NULL,
[StartDate] [datetime] NULL,
[ExpireDate] [datetime] NULL,
[Type] [int] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[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]
GO
SET ANSI_PADDING OFF
GO
May 9, 2018 at 12:26 pm
Phil Parkin - Wednesday, May 9, 2018 12:12 PMSomething like this, maybe:WITH SourceData
AS
(
SELECT
ID = ID
, EMPID = a.SNO
, TYPE = '1'
, ACTIVATE = a.StartDate
, DEACTIVATE = a.ExpireDate
, STATUS = '1'
, EMPKEY = ROW_NUMBER() OVER (ORDER BY
(
SELECT NULL
)
) + 1000
FROM SCS.DBO.EMPLEVEL a
)
INSERT SEC.dbo.EMPREC
(
ID
, EMPID
, TYPE
, ACTIVATE
, DEACTIVATE
, STATUS
, EMPKEY
)
SELECT
SourceData.ID
, SourceData.EMPID
, SourceData.TYPE
, SourceData.ACTIVATE
, SourceData.DEACTIVATE
, SourceData.STATUS
, SourceData.EMPKEY
FROM SourceData;
EMPKEY = ROW_NUMBER() OVER (ORDER BY
(
SELECT NULL
)
whats select null will do here ?
May 9, 2018 at 12:36 pm
aloshya - Wednesday, May 9, 2018 12:26 PMPhil Parkin - Wednesday, May 9, 2018 12:12 PMSomething like this, maybe:WITH SourceData
AS
(
SELECT
ID = ID
, EMPID = a.SNO
, TYPE = '1'
, ACTIVATE = a.StartDate
, DEACTIVATE = a.ExpireDate
, STATUS = '1'
, EMPKEY = ROW_NUMBER() OVER (ORDER BY
(
SELECT NULL
)
) + 1000
FROM SCS.DBO.EMPLEVEL a
)
INSERT SEC.dbo.EMPREC
(
ID
, EMPID
, TYPE
, ACTIVATE
, DEACTIVATE
, STATUS
, EMPKEY
)
SELECT
SourceData.ID
, SourceData.EMPID
, SourceData.TYPE
, SourceData.ACTIVATE
, SourceData.DEACTIVATE
, SourceData.STATUS
, SourceData.EMPKEY
FROM SourceData;
EMPKEY = ROW_NUMBER() OVER (ORDER BY
(
SELECT NULL
)
whats select null will do here ?
For whatever reason MS won't let you use OVER() without an order by clause, so that's just to trick SQL Server into generating the row numbers without any real order.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply