0

I have to check for the duplicate records before inserting the data into sql table.

I am getting the data/records from flat file

So here is the example, if the table column is blank/has some other values i am inserting the multiline text after checking duplicate criteria like below

select * from table1 where column1 like '% [testing query]

in Multiple Lines   %'

So if my next record also has the same/similar text it should not insert since a similar record has already been inserted. after checking the duplicate criteria as below

select * from table1 where column1 like '% [testing query]

in Multiple     %'

I tried using % operator but it is not checking for the duplicates. Please let me know if i am doing anything wrong or do i have to use any other method to check the duplicates

sra1
  • 37
  • 7

1 Answers1

0

This seems to work for me:

create table table1 (
    column1 nvarchar(max)
)
go
insert table1 (column1) values ('xx testing query

in Multiple Lines   xx'),('yy testing query

in Multiple     yy')
go
select * from table1 where column1 like '% testing query

in Multiple Lines   %'  
go
select * from table1 where column1 like '% testing query

in Multiple     %'
go
drop table table1
go

What type and version of SQL Server are you using? What data type is column1? (i.e.: varbinary will be sensitive to spaces whereas n/varchar may not be, depending upon Unicode your collation.)

AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • thanks for the quick reply, I am using Sql server 2008 R2, The datatype for the column is text and not varchar. – sra1 Apr 04 '14 at 03:34
  • @sra1: Why are you using `text` and not `varchar(max)` ? "ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them." Source: http://technet.microsoft.com/en-us/library/ms187993.aspx + http://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type – Bogdan Sahlean Apr 04 '14 at 03:49
  • @Bogdan I tried some thing like below but still it is not working `select * from table1 where CAST(column1 as Varchar (Max)) like '% [testing query] in Multiple Lines %'` – sra1 Apr 04 '14 at 03:54
  • I don't think you had the square brackets in your question when I answered it yesterday (since I copied my query from your question). Please read the "LIKE (Transact-SQL)" topic in SQL Books online to understand the significance of [] inside LIKE queries. – AlwaysLearning Apr 05 '14 at 13:31