CODEWITHSUNDEEP
sqlsql-server-2008
vbNewbie
vbNewbie

Reputation: 3345

sql server insert records from one table to another

how does one insert records from one table to another that has a unique index in the destination table without going through the insert and then removal of duplicates by deleting the index?

INSERT INTO forms(url,feedUrl, dateadded)
SELECT url, feedurl, dateadded
FROM Book3 T2
where not exists(select * from forms T1 where T1.url = T2.url;
T2.feedurl = T1.feedUrl and  T2.dateadded =T1.dateadded)

Violation of UNIQUE KEY constraint 'IX_forms'. Cannot insert duplicate key in object 'dbo.forms'.

Table forms

CREATE TABLE [dbo].[forms](
[id] [int] IDENTITY(1,1) NOT NULL,
[url] [varchar](450) NULL,
[feedUrl] [varchar](450) NULL,
[dateadded] [datetime] NULL,
 CONSTRAINT [PK_forms] PRIMARY KEY CLUSTERED 
 (

Table book3

CREATE TABLE [dbo].[Book3](
[url] [varchar](450) NULL,
[feedurl] [varchar](450) NULL,
[dateadded] [datetime] NULL
) ON [PRIMARY]

Upvotes: 0

Views: 3784

Answers (1)

HLGEM
HLGEM

Reputation: 96572

You may have duplicates in your results set. Does this query give you fewer records than the orginal select?

SELECT distinct url, feedurl, dateadded 
FROM Book3 T2 
where not exists(select * from forms T1 where T1.url = T2.url 
T2.feedurl = T1.feedUrl and  T2.dateadded =T1.dateadded)

Upvotes: 2

Related Questions