Trying to insert from table 1 with missing index to table 2 with index

Question

Post reply

Trying to insert from table 1 with missing index to table 2 with index

roy.tollison

SSCommitted

Points: 1930
More actions
September 11, 2019 at 3:18 pm

#3679157

Table 1 is missing an index that was supposed to be in place when the table was created that is set to unique. The table has an identity column. I have created the table 2 new with the index in place and is empty. (Table 1 and 2 are the same structure except table 1 is missing the index) Due to some performance issues i discovered the missing index and tried to add it but got error due to duplicate index values.
There is a column that represents a sequence number that i can increment if needed.
table1 & table2
col1 char(5)
col2 int
col3 char(10)
col4 smallint
Column 4 can be incremented when inserting from table 1 to table 2 but i am not sure how to catch that error and implement it.
NOTE the col4 could be duplicated 2+ times in the table 1
begin try
insert into table2 select * from table1
end try
begin catch
insert into table2 select col1, col2, col3, col4 + 1 from table1
end catch
But in the catch routine i get a duplicate again

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

What is your question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76996 More actions · Answer 2

You're missing a bunch of information.

Which column is the identity column?
Which column(s) do you want the unique constraint on?
Is the unique column a natural key or an artificial key?
That is, can we change it (artificial) or does it need to be preserved (natural)?
Does one "dup" override another?
Sample data (as a create temp table [or declare table variable] and an insert statement).
Expected results (as a create temp table [or declare table variable] and an insert statement).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

roy.tollison SSCommitted Points: 1930 More actions · Answer 3

the Identity column is not col1 thru col4, it is col12

I am just trying to insert from table1 to table2 (with a unique index) but i get duplicate errors when i try. I am just not good enough to figure out how to catch and insert those duplicate indexes

Col1, col2, col3, col4, ..., col12

AAA, 104, AAAAAA, 0, ...., 1

AAA, 104, AAAAAA, 0,...., 2

AAA, 104, AAAAAA, 1,...., 3

how should i go about inserting those values to the table2 but change the col4 values to 0,1,2.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

roy.tollison wrote:

the Identity column is not col1 thru col4, it is col12
I am just trying to insert from table1 to table2 (with a unique index) but i get duplicate errors when i try. I am just not good enough to figure out how to catch and insert those duplicate indexes
Col1, col2, col3, col4, ..., col12
AAA, 104, AAAAAA, 0, ...., 1
AAA, 104, AAAAAA, 0,...., 2
AAA, 104, AAAAAA, 1,...., 3
how should i go about inserting those values to the table2 but change the col4 values to 0,1,2.

Forget about the INSERT and concentrate on the SELECT for now. In the SELECT, your new col4 definition can probably make use of ROW_NUMBER(), but without items (5) and (6) referred to in Drew's post, I can't write the query for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76996 More actions · Answer 5

You only answered question 1. You have not addressed the other issues. Specifically note that a list does not qualify as "a create temp table [or declare table variable] and an insert statement".

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

carlanders Grasshopper Points: 21 More actions · Answer 6

It sounds like col12 is the identity, but col4 also needs to be unique too; if col4 does not need to be maintained for any integrity, then the row_number should work - does the value of Col12 also need to be maintained for integrity or can it be re-generated?

If col12 can be regenerated as well as col 4 (which means they will end up having the same value), this query should work:

insert into table2 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11)
select col1, col2, col3, row_number() over (order by col4 asc), col5, col6, col7, col8, col9, col10, col11 from table1

if Col12 needs to be maintained and is already unique, then

Change the over(order by XXX) for the desired sort criteria that you want things to be managed

roy.tollison SSCommitted Points: 1930 More actions · Answer 7

Sorry i know this is probably childs play for some of you but i have a long ways to go.

Thanks for the help, it is done and moving on to next round of issues.

Again thank you all very much.