0

I have a table that has multiple work records for each day. The reason there is no primary key is because there are multiple records showing each worker each day, and each person works multiple days.

I wrote a query that uses "into" to drop it into a table. The query is very complex so I figured it would be easier to just modify the table.

I wouldn't be having any problems but the table is returning duplicate rows. If I could assign a number to each unique row (or if there's another way that'd be great), I could select the distinct ID number and there'd be no duplicates.

I have tried select distinct * but it doesn't work.

Here is the table:

CREATE TABLE [dbo].[liaisonperformance](
    [sr_name] [varchar](50) NULL,
    [workdate] [date] NULL,
    [Calls_in] [int] NULL,
    [Calls_Out] [int] NULL,
    [Chats] [int] NULL,
    [reaches] [int] NULL,
    [books] [int] NULL,
    [attends] [int] NULL,
    [hoursworked] [float] NULL
) ON [PRIMARY]
GO
Mitchell Fortman
  • 9
  • 3

2 Answers2

0

You problably want to use a primary key that spans over multiple columns, such as sr_name and workdate. An alternative solution would be to have a column id INT IDENTITY(1,1) PRIMARY KEY. The second solution is called a surrogate key, whereas the first one is a natural key (I assume that the combination sr_name and workdate is always unique)

H3llskrieg
  • 94
  • 8
  • I just took the steps of altering 'sr_name' and 'workdate' to be not null. I then did this: `alter table workdb.dbo.liaisonperformance ADD CONSTRAINT constraint_name primary key (sr_name, workdate)` but I got this error: _The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.liaisonperformance' and the index name 'constraint_name'. The duplicate key value is (Agnes K., 2017-02-07). Msg 1750, Level 16, State 1, Line 1 Could not create constraint or index. See previous errors._ – Mitchell Fortman Jul 18 '19 at 17:03
  • is there any unique/nonclustered index on this table already? – H3llskrieg Jul 18 '19 at 17:07
  • What do you mean by that? – Mitchell Fortman Jul 18 '19 at 17:09
  • oh wait nevermind. I think there is duplicate data in your table, is this the case? `SELECT * FROM liaisonperformance GROUP BY sr_name, workdate HAVING COUNT(*) > 1` Does this give any results? Rereading your error I am 100% sure your data contains duplicates values for the combination I thought was unique. – H3llskrieg Jul 18 '19 at 17:11
  • You will need to delete records that are more then once in your database, or your primary key needs to be more specific – H3llskrieg Jul 18 '19 at 17:16
  • This works in deleting the duplicates but for some reason the data is coming up wrong. There's too many rows missing. – Mitchell Fortman Jul 18 '19 at 17:19
  • https://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one should do the trick – H3llskrieg Jul 18 '19 at 17:24
0

I would suggest defining the table as:

CREATE TABLE [dbo].[liaisonperformance] (
    liaisonperformance_id int identity primary key,
    [sr_name] [varchar](50) NULL,
    [workdate] [date] NULL,
    [Calls_in] [int] NULL,
    [Calls_Out] [int] NULL,
    [Chats] [int] NULL,
    [reaches] [int] NULL,
    [books] [int] NULL,
    [attends] [int] NULL,
    [hoursworked] [float] NULL
) ;

If you are using select . . . into, then use the identity() function:

select identity(int, 1, 1) as liaisonperformance_id,
       . . .
into liaisonperformance_id
from . . .;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You would add data though, which is not alsways what you want. Consider reading about surrogate keys first https://www.sisense.com/blog/when-and-how-to-use-surrogate-keys/ – H3llskrieg Jul 18 '19 at 17:13
  • Will this assign each row the row number that it is? If so, this won't change anything because there will still be rows that have all the same values but different `liaisonperformance_id` numbers – Mitchell Fortman Jul 18 '19 at 17:13
  • You will need to add a unique constraint over your unique combination then – H3llskrieg Jul 18 '19 at 17:14
  • @MitchellFortman . . . You will not be able to insert rows into the table if you create a unique constraint on columns that have duplicates. – Gordon Linoff Jul 18 '19 at 18:16