MS SQL - Update If Matching Data (Maybe No PK)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MS SQL - Update If Matching Data (Maybe No PK)

Author

Topic

doctorzeus
Starting Member

2 Posts

Posted - 2015-02-04 : 21:57:27

Hello,

Basically without going into too much detail, our company gets databases arriving and put onto our systems which have been made my other organizations with no guarantee of what the primary key is or if this is one at all.

I should probably give my main problem in an example for clarity:

Currently I have a .csv file full of data that needs to be put into say TableA. However I do not know if TableA has a primary key or not, or if the file that needs to be inserted into TableA contains duplicate data. I have the importer sorted that does this if you ignore the problem of duplicate data, however what I would like is an MS SQL query that does the following (but I cannot figure it out):

Assuming we are reading through the file line-by-line and a check is performed each time:

1.If there is a line with a primary key in the file that matches a primary key in TableA in the database update that row in the database with the line in the file.

2. If there is no primary key on the table and there is an exact data match between the line in the file and a row in the database then update it.

3. If neither 1 or 2 are successful then just insert the data.

Sorry for the confusion but obviously the potential lack of a PK here makes things a lot more convoluted (please ask if you need more info).

Kind Regards

DoctorZeus

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 09:03:04

1. First off, you can see if a table has a primary key with the sys.indexes view, e.g.


select OBJECT_NAME(object_id), * from sys.indexes where is_primary_key = 1

but how do you know which field in the csv file has the primary key?

You can do a conditional insert/update. Assuming you have loaded your csv file to a table:


updated tablea
set col1 = csv.col1
from tablea
join csv on tablea.pk = csv.pk

2. use a WHERE clause that matches all the columns instead of just the PK

3. Conditional insert:



insert into tablea(...)
select * from csv
where not exists (
    select 1 from tablea
    where csv.col1 <> tablea.col2 or csv.col2 <> tablea.col1, ...
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources