Insert data best practice 2

[glenninflorida]

Hi all:

I'm in need of the best practice for the following:

I need to update the destination table with only new records from a source table. I can use the following to append "new / non-existant" records.

INSERT INTO destination
SELECT user, status
FROM source
WHERE NOT EXISTS
(SELECT *
FROM destination
WHERE ISNULL(destination.user,'XX') = ISNULL(source.user,'XX'))


However..., I need to manipulate the data along the way since and one of the fields (status) has a different data type and value.

source.status type = text , values are "A" and "I"

destination.status type = bit , values are 0 and 1 (stating the obvious here

I'm thinking I will need to use a cursor since each row must be tested and manipulated.

or I could create an extra column in destination table and create an sproc that:
1) does the insert statment above
2) does an update statement from extra column to status

I think (based on very little MS-SQL experience) the cursor in an sproc would be cleaner, I've googled various cursor examples but have not found a similar example that I can use to learn from.

Your input is greatly appreciated.
Thanks

 

[SQLDenis]

does this do what you want?

INSERT INTO destination
SELECT user, case status when 'A' then 1 else 0 end status
FROM source
WHERE NOT EXISTS
(SELECT *
FROM destination
WHERE ISNULL(destination.[user],'XX') = ISNULL(source.[user],'XX'))

what happens when destination.[user] ='XX'
or
source.status is not A or I

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You can do some manipulations on the fly, you just need to know how.

In this example, I assume you want 1 when the source status is 'A' and 0 otherwise. This may not be exactly what you are looking for, but hopefully it will give you a few ideas.

INSERT INTO destination
SELECT user, [!]Case When status = 'A' Then 1 Else 0 End[/!]
FROM source
WHERE NOT EXISTS
(SELECT *
FROM destination
WHERE ISNULL(destination.user,'XX') = ISNULL(source.user,'XX'))

Oh, and remember, Cursors are a last resort. Almost anything you want to do can be accomplished with a set based approach.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom