Preventing doubling up of database
-
What is the best way of preventing a table being imported directly onto an identically named table? This creates duplicate rows and can be a bother to fix.
-
1. Put a constraint on the table to produce error for duplicates
2. Change the INSERT statement to only insert rows not already present
(use LEFT OUTER JOIN)
Far away is close at hand in the images of elsewhere.
Anon. -
Does anyone please have any examples of these?
-
1.
ALTER TABLE
ADD CONSTRAINT [constraintname] UNIQUE NONCLUSTERED ([column1],[column2])
Look up 'constraints, UNIQUE' in Books Online (BOL)
2.
INSERT INTO
([column1],[column2])
SELECT a.[column1],a.[column2]
FROM [anothertable] a
LEFT OUTER JOIN
b
ON b.[column1] = a.[column1]
AND b.[column2] = a.[column2]
WHERE b.[column1] IS NULL
Far away is close at hand in the images of elsewhere.
Anon. -
What about triggers? Could each table have a trigger that prevents it happening?
-
Yes, use an INSTEAD OF trigger
CREATE TRIGGER [triggername] on
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO
([column1],[column2])
SELECT a.[column1],a.[column2]
FROM [inserted] a
LEFT OUTER JOIN
b
ON b.[column1] = a.[column1]
AND b.[column2] = a.[column2]
WHERE b.[column1] IS NULL
END
This will only insert non existing rows and ignore the rest
alternatively you could check if any duplicates and issue a RAISERROR to produce an error or insert the valid data
btw has this table not got any primary keys?
Far away is close at hand in the images of elsewhere.
Anon. -
The table in the source database had a key that was specified as an IDENTITY column. But for some reason when I transferred that table it lost its’ status as an identity column. If it had the IDENTITY property set would that make it a primary key? I don't think it does it. Please inform me. I have produced an analytical database. The base tables are not therefore linked. They are just there to be used by the SPROCs. But it would make sense to have these base tables protected. It would also make sense to enforce certain characteristics such as age group categories. And I guess that this could be done with a proper schema. Any thoughts? I guess that I am asking for a good motivationaly telling off.
-
I think that I remember what I did before. I set the identity key as a primary key.
-
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
when this column is specified as NOT FOR REPLICATION does that make this column a non clustered primary key? I can't see any difference. Also, why is this property lost when it is transferred (as part of a table) between different databases?
-
Setting a column to be an IDENTITY column will not make it a primary key. The primary key of a table is for you to decide. Making an IDENTITY column a PRIMARY KEY will not stop duplicate data only the IDENTITY column itself but then the IDENTITY will make it unique anyway (unless you use SET IDENTITY_INSERT to override the identity). Normally you would choose the 'natural' key of the table to be the PRIMARY or if there is not one then the IDENTITY column. A table does not have to have a PRIMARY KEY, there are a lot of threads on this site that discuss the pros and cons of PRIMARY KEYS.
to enforce certain characteristics such as age group categories This is one of the uses for constraints, to enforce data integrity.
Which method you use depends on your personal preference and what the definition of 'duplicate' is. I would still advocate applying constraints to stop duplication or a INSTEAD OF trigger if you do not want to produce any errors
Far away is close at hand in the images of elsewhere.
Anon. -
when this column is specified as NOT FOR REPLICATION does that make this column a non clustered primary key No, it does as it states, it stops the column being replicated so that destination databases can have their own IDENTITY values
why is this property lost when it is transferred I presume because the setting is for that particular database's replication. Not sure really but is sort of makes sense.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply