February 22, 2023 at 8:30 pm
I administer a database for a local chapter of a national group. The National group does not provide local chapters access to a database but does give us a csv file which i then used to export to an Excel spreadsheet with all the members listed in it. I then imported that table into my database. Using that I wrote a query and said to select only those members that are not found in the members table, and then tried to insert them . This is where I am getting a duplicate key error. There is absolutely no instance of the key in the table that I am trying to insert the record into
here is the Members table
CREATE TABLE [dbo].[Members](
[MemberNumber] [int] NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[Title] [nvarchar](10) NULL,
[Address1] [nvarchar](100) NOT NULL,
[Address2] [nvarchar](50) NULL,
[City] [nvarchar](50) NOT NULL,
[PostalCode] [varchar](10) NOT NULL,
[Email] [nvarchar](100) NULL,
[Phone] [varchar](13) NULL,
[SinceDate] [date] NOT NULL,
[ExpirationDate] [date] NULL,
PRIMARY KEY CLUSTERED
(
[MemberNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
and then for the data imported from excel
CREATE TABLE [dbo].[localgroupmembershiprostercsv__$](
[member_number] [bigint] NULL,
[title] [nvarchar](255) NULL,
[first_name] [nvarchar](255) NULL,
[middle_name] [nvarchar](255) NULL,
[last_name] [nvarchar](255) NULL,
[address_line_1] [nvarchar](255) NULL,
[address_line_2] [nvarchar](255) NULL,
[city] [nvarchar](255) NULL,
[postal_code] [nvarchar](255) NULL,
[phone_number] [nvarchar](255) NULL,
[emailaddress] [nvarchar](255) NULL,
[since_date] [datetime] NULL,
[mbr_expire_date] [datetime] NULL
) ON [PRIMARY]
GO
and here is my query to insert the records. to ensure that only new records were being inserted i created a table variable and looked at all of the records and made sure none of them were in the members table
DECLARE @Temp Table
(
MemberNumber int,
FirstName nvarchar(50),
LastName nvarchar(50),
MiddleName nvarchar(50),
Title nvarchar(10),
Address1 nvarchar(100),
Address2 nvarchar(50),
City nvarchar(50),
PostalCode nvarchar(10),
Email nvarchar(50),
Phone nvarchar(13),
SinceDate date,
ExpirationDate date
)
INSERT @Temp
SELECT
CAST(member_number as INT),
[first_name],
[last_name],
[middle_name],
[title],
[address_line_1],
[address_line_2],
[city],
[postal_code],
[emailaddress],
[phone_number],
CAST(since_date as date),
CAST(mbr_expire_date as date)
FROM
[dbo].[localgroupmembershiprostercsv__$] WHERE member_number NOT IN (SELECT MemberNumber FROM dbo.Members)
Insert Members
SELECT * FROM @Temp
And yet it throws an error on the very first record in the table variable even though that record is NOT in my members database
February 22, 2023 at 8:38 pm
member_number is a bigint in [localgroupmembershiprostercsv__$], & MemberNumber is only an int in @Temp & Members? Are any of the member #s larger than an int & possibly truncated (which would probably result in duplicates).
Since the table is empty, the error indicates that the duplicates exist within the source. Have you tried sorting your Excel sheet and looking for rows w/ the same member_number? Have you verified that member_number is not being truncated?
February 22, 2023 at 9:00 pm
ARGH I saw what i did i had duplicated the import into the [localgroupmembershiprostercsv__$] table so then when i ran my query it would insert and then find the duplicate entry from that table and try to insert again
Feeling really stupid right now
February 23, 2023 at 2:53 pm
I've never made a dumb mistake like that.... well ... except for that time, and that other time, and then there was that other time also .....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply