COPYING data between databases.

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

COPYING data between databases.

Author

Topic

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-16 : 07:17:28

Hi All,
I wish to copy between 2 tables in different databases.
( The source and destiantion tables already exist)
The source table has duplicate entries which i want to avoid from being copied to destination table.
How can i achieve this?

Thanks in advance..!!
Vishu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 07:19:49

Insert into targetdb.dbo.table(columns)
select distinct columns from sourcedb.dbo.table

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:21:01

Insert {Target}
SELECT DISTINCT Col1, Col2, Col3, ...
FROM {Source}

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 07:22:10

E 12°55'05.25"
N 56°04'39.16"

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-16 : 22:42:39

Hi
i tried the above all possibilities. Its STILL says the 'voilation of the Primary key.... cannot insert
duplicate records'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 23:22:22

[code]
IF NOT EXISTS ( SELECT * FROM ..)
BEGIN
INSERT INTO ...

END
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-16 : 23:31:16

Hi dinakar,
It doesnt work like that..!!!
I still cannot insert any data.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-16 : 23:53:35

quote:
Originally posted by vishu_av

Hi dinakar,
It doesnt work like that..!!!
I still cannot insert any data.

Can you post what you have tried?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 00:13:43

IF NOT EXISTS (SELECT * FROM database1.DBO.tablename)
BEGIN
INSERT INTO database2.DBO.tablename(column1,column2,column3,.....)
SELECT DISTINCT column1,column2,column3,.....
FROM database1.DBO.tablename

The above code executes with success but doesnt insert any data in the destination table

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 00:59:36

[code]
INSERT INTO database2.dbo.tablename(column1,column2,column3,.....)
SELECT DISTINCT column1,column2,column3,.....
FROM database1.dbo.tablename AS S
WHERE NOT EXISTS (SELECT * FROM database2.DBO.tablename AS D WHERE D.PKColumn = S.PKColumn)
[/code]
Kristen

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 01:12:17

Hi kristen,
Sorry to say... I still get the same error..!!!
something like below.....

Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:33:51

Do you have ALL the Primary Key columns listed in your WHERE clause?

probably best if you post the exact code you are using here so we can have a look at it.

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 04:24:50

here is my table1 in BancoEstado

USE [database1]
GO
/****** Object: Table [dbo].[ETI_IDS] Script Date: 10/17/2007 13:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ETI_IDS](
[ETI_ENTITYID] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ETI_IDTYPE] [decimal](5, 0) NOT NULL,
[ETI_SEQNUM] [decimal](5, 0) NOT NULL,
[ETI_IDTYPE_CD] [decimal](5, 0) NOT NULL,
[ETI_IDNUMBER] [nvarchar](160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ETI_ISSUEPLACE] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ETI_ISSUEPLACE_CD] [decimal](5, 0) NULL,
[ETI_ISSUEDATE] [datetime] NULL,
[ETI_EXPIRYDATE] [datetime] NULL,
[ETI_NXTVERDATE] [datetime] NULL,
[ETI_STATUS] [decimal](5, 0) NULL,
[ETI_STATUS_CD] [decimal](5, 0) NULL,
[ETI_USERID] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ETI_MODIFYTIME] [datetime] NULL,
[ETI_MODIFYSEQNO] [decimal](12, 0) NULL,
CONSTRAINT [PK_ETI_IDS] PRIMARY KEY CLUSTERED
(
[ETI_ENTITYID] ASC,
[ETI_IDTYPE] ASC,
[ETI_IDTYPE_CD] ASC
) ON [PRIMARY]
) ON [PRIMARY]

and similar table in database2
i have around 226 rows in database1 and i am using following code

INSERT INTO database2.DBO.ETI_IDS
SELECT DISTINCT * FROM database1.DBO.ETI_IDS AS S
WHERE NOT EXISTS (SELECT * FROM database2.DBO.ETI_IDS AS D WHERE
D.ETI_ENTITYID = S.ETI_ENTITYID AND D.ETI_IDTYPE = S.ETI_IDTYPE AND D.ETI_IDTYPE_CD = S.ETI_IDTYPE_CD)

i have also tried using column names as mentioned in earlier posts.

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 04:25:35

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 04:41:23

Well I'm stuck then, I can't see how that can insert a duplicate.

Slim chance that putting it in a sub-select will isoolat the duplicates better, but if it DOES work I won't be able to explain why. Wortha shot though:


INSERT INTO database2.DBO.ETI_IDS
SELECT *
FROM
(
    SELECT DISTINCT * FROM database1.DBO.ETI_IDS
) AS S
WHERE NOT EXISTS (SELECT * FROM database2.DBO.ETI_IDS AS D WHERE
D.ETI_ENTITYID = S.ETI_ENTITYID AND D.ETI_IDTYPE = S.ETI_IDTYPE AND D.ETI_IDTYPE_CD = S.ETI_IDTYPE_CD)

Kristen

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 04:49:52

I forgot to mention the destination table1 has the PKs but the source table2 doesnt have any PKs.
The above Table script is that of the destination table.
Needless to say the source table has duplicate entries for the above said clustered primary keys.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 05:19:31

Ah ... so for a given

ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD

combination on the Source Database there may be multiple records. You will need to resolve WHICH of the multiple records gets copied across. The DISTINCT is only preventing duplicate rows being copied which are identical in ALL columns

Kristen

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 05:23:55

YA.. I GUESS THATS A TRICK THERE...
CAN I KNOW HOW TO DELETE THE SUCH DUPLICATE ENTRIES IN THE SORCE DATABASE.
I WISH TO DELETE THE DUPLICATE ENTRIES FOR COMBINATION OF THE KEYS(COLUMNS FOR A CLUSTERED KEYS)

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 06:09:05

So for a duplicate records (with the same ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD) which one has the data you want to keep?

Seems to me that there is a reason why there are duplicates, and just keeping one version, at random, is probably a poor method!

Kristen

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2007-10-17 : 08:49:15

If i run
SELECT DISTINCT ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD FROM database1.DBO.ETI_IDS
against
SELECT * FROM FROM database1.DBO.ETI_IDS... I get less records. I wish to eliminate the records that are not fetched by SELECT DISTINCT ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD FROM database1.DBO.ETI_IDS and insert the rest.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-17 : 09:46:14

as Kristen has said, you need to decide which dupes to leave out. Here's a query that will show you the dupes. It's then up to you to delete the ones you don't want:


select * from ETI_EDS eti
join (select ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD 
      from ETI_EDS 
      group by ETI_ENTITYID, ETI_IDTYPE, ETI_IDTYPE_CD 
      having count(*) > 1) as dupes
 on dupes.ETI_ENTITYID=eti.ETI_ENTITYID
and dupes.ETI_IDTYPE=eti.ETI_IDTYPE
and dupes.ETI_IDTYPE_CD=eti.ETI_IDTYPE_CD

elsasoft.org

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources