Copying data - SQL Server Forums

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

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-10 : 07:33:31

Rene writes "Good evening. This is my first time using sqlteam for answers and I'm hoping I can get some much needed direction.

Basically what I am trying to accomplish is taking records from a temporary source table to a permanent table. Here is what the tables look like (oversimplistic version)

tblTemp

ID
Value1
Imported
FailedReason

tblPerm

ID
Value1

Basically I would like to take the values from tblTemp and INSERT them into tblPerm. The catch is that values in tbltemp might violate primary key constraint because of duplicate values in the ID field. The value1 field is required in tblPerm and it might contain a null value on tbltemp causing the insert statement to fail.

What I would like the end result to be is that any records which are INSERTED from tbltemp to tblperm are flagged with a value of imported=1 on the tbltemp table. Any records which fail should then be flagged as imported=0 and failedreason=reason for failing.

I am trying the following to start with but am not sure if I am steering in the right direction or not. The process should be as automated as possible, perhaps part of a scheduled dts package or likewise since new data will be inserted in the tbltemp table on a weekly basis.

set rowcount 1

update tblsource set imported = 0 where imported is null

insert into tbldest (col1, col2)
select top 1 col1, col2 from tblsource where imported = 0 and notimportreason is null

IF @@Error <> 0
GOTO ErrorHandler

UPDATE tblsource SET imported = 1 where imported = 0 and notimportreason is null

ErrorHandler:

update tblsource SET notimportreason = @@error where imported = 0 and notimportreason is null

RETURN"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-10-10 : 08:18:04

--Create a procedure, add to your DTS.

--Check for PK violations
UPDATE T
SET Imported = 0
, FailedReason = 'Existing Record/Duplicate Key'
FROM tblTemp T
INNER JOIN tblPerm P ON T.Value1 = P.Value1
WHERE Imported IS NULL

BEGIN TRAN
--Insert records
DECLARE @chkError INT, @chkCount INT, @chkCount2 INT
SET @chkError = 0

INSERT INTO tblPerm ...
SELECT ...
FROM tblTemp T
LEFT OUTER JOIN tblPerm P ON T.Value1 = P.Value1
WHERE P.Value1 IS NULL

SELECT @chkError = @@ERROR, @chkCount = @@ROWCOUNT

UPDATE tblTemp
SET Imported = 1
WHERE Imported IS NULL

SELECT @chkError = @chkError + @@ERROR, @chkCount2 = @@ROWCOUNT

IF @chkError = 0 AND @chkCount = @chkCount2
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
...
END

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-10-10 : 08:27:40

Having any table without a primary/key is a sign of bad design in my book, and if you don’t want null values in your permanent table why allow them in your temp?
I have always found that it is almost always better to fix a problem at the source rather than Band-Aid the problem later. Also when we talk about a temp table in SQL we are usually talking about a table that is created and dropped in a current session. Looks to me like your talking about a holding table of some sort. Is it possible to fix this at the data entry/import stage (weekly)? How are you getting the data in the first place?

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources