insert missing rows from table1 to table2, both use identity columns

Question

insert missing rows from table1 to table2, both use identity columns

roy.tollison

SSCommitted

Points: 1930
More actions
August 30, 2010 at 12:43 pm

#100302

i am writing a routine in Delphi program (ado) that allows my users select 2 tables and finds the missing rows in one table and inserts them into another table. Here is the reasoning behind it all.
1) user copies table1 to table1_save
a) 'select * into table1_save from table1'
b) in the background my program also creates the indexes in table1_save based upon the indexes in table1
now the user runs another program that may delete rows from table1 or change rows in table1
2) an option on the screen of my program allows them to copy all the rows from table1_save back to table1. (already worked out the logic)
3) This option is the one i am trying to get going.
a) if the user simply needs to put any MISSING rows back into table1
b) different tables have different unique indexes (sometimes multiple unique indexes) and they all have an identity column
I need to...
1) get a list of the columns in the unique indexes
then i can create my command in delphi to look for missing rows
Is this the only way to insert random columns back into table1 or is there another way?

Viewing 5 posts - 1 through 4 (of 4 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

To reinsert the rows that were copied from table1 into table1_save, and subsequently deleted from table1, keeping the original identity value:

SET IDENTITY_INSERT table1 ON

INSERT INTO table1 (list of all columns INCLUDING the identity column)

SELECT (list of all columns)

FROM table1_save t1s

LEFT JOIN table1 t1

ON t1.PK = t1s.PK

WHERE t1.PK IS NULL -- t1.PK is not in the table

SET IDENTITY_INSERT table1 OFF

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

That's pretty much what i have for inserting all records from table1_save back to table1 after deleting all the records from table1, with the exception of the test on the .PK.

these tables don't have a PK (unique indexes and identity column). So there in lies my stumbling block. how do i get a listing of the columns that are in a unique index. possibly multiple indexes have unique option.

its a very unorganized database.

uses unique indexes as a means to address a PK but uses an identity column.

is there anything in the syscolumns that indicates that a column is in a unique index or am i going to have to go thru sysindexes figuring out which ones are unique?

WayneS SSC Guru Points: 95460 More actions · Answer 3

Does this get you the data that you're looking for?

SELECT [IndexName] = so.name,

[IndexPos] = sik.keyno,

[Column Name] = sc.name

FROM sysindexes si

JOIN sysobjects so

ON si.id = so.parent_obj

AND si.name = so.name

AND so.xtype = 'uq'

JOIN sysindexkeys sik

ON si.id = sik.id

AND si.indid = sik.indid

JOIN syscolumns sc

ON sc.id = si.id

AND sik.colid = sc.colid

ORDER BY so.name, sik.keyno

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

that will work for what i need. now i can just use that information to link the 2 tables missing rows.