In T-SQL you may use “insert” to remove duplicates. What you must do is create another work table with the IGNORE_DUP_KEY option set.

So you copy the data from one table to the other. Drop the original table and rename the work table to your original table name and that's it. You will have a plain, clean, non duplicated records table. Something like:


CREATE TABLE tableCleanDup
(idfield int, field1 varchar(30), field2 varchar(30))
CREATE UNIQUE INDEX removeduplicates ON tableCleanDup (field1,field2) WITH IGNORE_DUP_KEY
GO
INSERT tableCleanDup SELECT * FROM tableOriginal


It will return to you a message “duplicate key was ignored” but that is fine. The tableCleanDup will have the records from tableOriginal but without duplicates.

p.s.: Thanks to Ken Henderson for this T-SQL advice.

Vers

Sometimes we face issues on our jobs where we can't apply the right thory that we learned from the books and at school about database design.

Algumas vezes encontramos situações no trabalho onde não podemos aplicar a teoria correta no desenho do Banco de Dados.

In today's T-SQL tip, with the help of a DBA friend I have, I will demonstrate how to return a recordset from two tables where in one of them the content for a field is a list of IDs.

Na dica de T-SQL de hoje, com a ajuda de um amigo DBA, eu demostro como retornar um recordset apartir de duas tabelas onde em uma delas o conteúdo dos campos é uma lista de IDs.

Supose that the database tables structure can't be modified to a more normalized and theoretically correct. So imagine this two sample tables:

 Supondo que o banco de dados não pode ser alterado para uma forma normalizada teóricamente mais eficiente. Imagine essas duas tabelas:

CREATE TABLE [dbo].[products]
([id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]
GO
-- insert some sample data
INSERT INTO products (name)
VALUES ('product 1')
GO
INSERT INTO products (name)
values ('product 2')
GO
INSERT INTO products (name) values ('product 3')
GO
INSERT INTO products (name) values ('product 4')
GO
INSERT INTO products (name) values ('product 5')
GO
CREATE TABLE [dbo].[orders]
( [id_order] [int] IDENTITY (1, 1) NOT NULL ,
[products] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO
-- insert some sample data
INSERT INTO orders (products) VALUES ('1,3,4')
GO
INSERT INTO orders (products) VALUES ('2,3,5')
GO
INSERT INTO orders (products) VALUES ('3,4')
GO
INSERT INTO orders (products) VALUES ('1,5')
GO
INSERT INTO orders (products) VALUES ('3')
GO
INSERT INTO orders (products) VALUES ('2')


Now the select statement that makes the join between the two tables and return the recordset for each products on the orders table

Agora, o select que faz a junção das duas tabelas e retorna um recordset com cada produto na tabela orders.


SELECT o.id_order, p.name
FROM orders o INNER JOIN products p ON CHARINDEX(','+CAST(p.id as varchar)+',',','+o.products+',') > 0
ORDER BY o.id_order


The secret is the use of charindex with the commas at the start and end to find the IDs in the orders table.

O segredo esta em usar o charindex com as virgulas (',') no inicio e no final para encontrar os IDs contidos na tabela orders.

Update 1: A friend requested to make the select for a MS Access database. I don't recommend Access for web applications but this is the subject for another post. See the code bellow .

Atualiza