importing data to a table having a unique key (moving records not inserted to a log table) and not aborting the operation

Question

Post reply

importing data to a table having a unique key (moving records not inserted to a log table) and not aborting the operation

Sarsoura

Ten Centuries

Points: 1220
More actions
May 9, 2010 at 8:14 pm

#220777

I receive my data thru an xml file. I imported the data to a temporary table . email,firstname,lastname,etc.
The data should move to a permante table that has data already.
id(identity) , email,firstname,lastname,etc. but the email is a unique key.
I want to append the data from the temporary table to the permante one without being aborted due to duplicate key problem and I want to move the records which were not being able to be inserted to another log table.
how can I do that in the fastest way because my xml files are huge the size is 1gb
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Have you already shredded the XML files into regular columns in the temp table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sarsoura Ten Centuries Points: 1220 More actions · Answer 2

Sarsoura

Ten Centuries

Points: 1220

May 9, 2010 at 10:26 pm

#1164809

yes

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

IIRC, SQL Server 2008 has the "5th" command in it... MERGE. Take a look at Books Online for the MERGE command. It'll do everything you ask in your original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sarsoura Ten Centuries Points: 1220 More actions · Answer 4

Thanks I will try the merge and I might come back to you if I got a question. Thanks again

Sarsoura Ten Centuries Points: 1220 More actions · Answer 5

Thanks Now I need to add the ones which were not inserted into a log table with the same columns

I tried the following but it did not work

create table #tempPerson ([UserID] [int] IDENTITY(1,1) NOT NULL,

[Email] [nvarchar](50) NOT NULL,

[Pass] [nvarchar](20) NOT NULL,

[FirstName] [nvarchar](20) NULL,

[LastName] [nvarchar](40) NULL,

[DisplayName] [nvarchar](50) NULL,

[Profile] [nvarchar](max) NULL,

[DisplayEmail] [nvarchar](50) NULL,

[CellPhone] [nvarchar](20) NULL,

[UpdatedBy] [int] NULL,

[UpdateDate] [datetime] NOT NULL,

[Deleted] [bit] NOT NULL)

....populating the temporary table -----

select * from #tempPerson

MERGE SP.UserTrial

USING (SELECT Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted from #tempPerson) AS source

ON (SP.UserTrial.Email = source.Email)

WHEN NOT MATCHED THEN

INSERT (Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted)

VALUES (source.Email,source.Pass,source.FirstName,source.LastName,source.DisplayName,source.[Profile],source.DisplayEmail,source.CellPhone,source.UpdatedBy,source.UpdateDate,source.deleted)

when Matched then

insert into longpersonerror(Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted)

values (source.Email,source.Pass,source.FirstName,source.LastName,source.DisplayName,source.[Profile],source.DisplayEmail,source.CellPhone,source.UpdatedBy,source.UpdateDate,source.deleted);

________________________________

I got an error Incorrect syntax near the keyword 'into'.