Insert Into or Update on bulk [Solved]

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert Into or Update on bulk [Solved]

Author

Topic

sql_dan
Starting Member

43 Posts

Posted - 2010-07-12 : 04:06:46

Morning,

I have a query that works on an individual basis to update or insert into a table that runs as a stored procedure when things are updated but what I would like to do is run a job overnight to update all of them to pick up any changes.

I would also like any new items to be inserted into the table.

The code I have to list everything is as follows:

quote:
SELECT DISTINCT ch.[key],ch.[episode],ch.unit_price AS [Total Invoiced]
FROM dbo.chrghist AS ch
LEFT JOIN dbo.DirectCosts AS dc ON dc.Patient_ID = ch.[key] AND dc.Episode_ID = ch.[episode]

The individual code I have amended this from is:

quote:

UPDATE dbo.Cent
SET Fees = (
SELECT unit_price AS [Total Invoiced]
FROM dbo.chrghist
WHERE invoice = (SELECT DISTINCT Invoice
FROM dbo.DirectCosts
WHERE Episode_ID = @epi) AND package = '')

IF @@ROWCOUNT=0

INSERT INTO dbo.Cent
SELECT @epi,
(
SELECT unit_price AS [Total Invoiced]
FROM dbo.chrghist
WHERE invoice = (SELECT DISTINCT Invoice
FROM dbo.DirectCosts
WHERE Episode_ID = @epi) AND package = '')

Obviously with variables defined within the query & stored procedure to only update the individual record.

Any help would be great!
Thanks
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Kristen
Test

22859 Posts

Posted - 2010-07-12 : 06:15:24

[code]
WHERE invoice = (SELECT DISTINCT Invoice
FROM dbo.DirectCosts
WHERE Episode_ID = @epi)
[/code]
looks a bit scary. I always worry when I see DISTINCT in a query as it often implies that a query returned multiple rows, and this was the "fix".

Does the inner query return multiple rows? If so the "=" equals test isn't going to work (I think you'll get a runtime error) - you will need "IN" instead of "=". If it returns multiple rows but NEVER multiple values for [Invoice] then I would use TOP 1 instead - faster than DISTINCT where the CPU has to select them all, sort, de-dupe and STILL wind up with only one row!

Do you have a ChangeDateTime column in the table? That's what we use for daily updates.

Get @PreviousLastChangeDateTime from a table that stores it from the last batch.
[code]
SELECT @NewLastChangeDateTime = MAX(ChangeDateTime) FROM MySourceTable -- Get a cutoff point (assuming records are changing as you do this update)

-- Update existing records
UPDATE D
SET D.Col1 = S.Col1, D.Col2 = S.Col2, ...
FROM MySourceTable AS S
JOIN MyDestinationTable AS D
ON D.ID = S.ID
WHERE S.ChangeDateTime >= @PreviousLastChangeDateTime
AND S.ChangeDateTime <= @NewLastChangeDateTime

-- Create new records
INSERT INTO
(
Col1, Col2, ...
)
SELECT S.Col1, S.Col2, ...
FROM MySourceTable AS S
LEFT OUTER JOIN MyDestinationTable AS D
ON D.ID = S.ID
WHERE S.ChangeDateTime >= @PreviousLastChangeDateTime
AND S.ChangeDateTime <= @NewLastChangeDateTime
AND D.D.ID IS NULL

-- You may need a DELETE phase - for records now deleted from MySourceTable

-- Save the last change date (as the start-date/time for the next batch)
UPDATE U
SET LastChangedDateTime = @NewLastChangeDateTime
FROM MyChangeDateTimeTable
WHERE MyKey = 'MySourceTable'
[/code]

sql_dan
Starting Member

43 Posts

Posted - 2010-07-12 : 07:25:22

Hi Kristen,

Your right the DISTINCT was a 'fix' which after I posted this managed to 'fix' properly with the addition of some AND clauses in the WHERE to get an absolute rather than what was returned!!

Thanks for the code but I only have a ChangedDateTime field in one of the tables not both so this wont work for me without changing the architecture!

Unless there is another way round it???
I was looking into the possibility of running a sproc in a loop for each row as I know the sproc works on an individual level!

Thoughts????

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Kristen
Test

22859 Posts

Posted - 2010-07-12 : 07:31:13

Sproc on each row will be slow.

You could compare all columns - so that you only "Update" rows that have changed.



UPDATE D
SET D.Col1 = S.Col1, D.Col2 = S.Col2, ...
FROM MySourceTable AS S
     JOIN MyDestinationTable AS D
         ON D.ID = S.ID
WHERE     S.ChangeDateTime >= @PreviousLastChangeDateTime
      AND S.ChangeDateTime <= @NewLastChangeDateTime
      AND (
		   (D.[Col1] COLLATE Latin1_General_BIN2 <> S.[Col1] OR (D.[Col1] IS NULL AND S.[Col1] IS NOT NULL) OR (D.[Col1] IS NOT NULL AND S.[Col1] IS NULL))
		OR (D.[Col2] COLLATE Latin1_General_BIN2 <> S.[Col2] OR (D.[Col2] IS NULL AND S.[Col2] IS NOT NULL) OR (D.[Col2] IS NOT NULL AND S.[Col2] IS NULL))
		...
      )

Use a combination of ChangeDateTime column and/or actual column changed tests. Only use "COLLATE Latin1_General_BIN2" on varchar/char columns, not INT/Datetime/etc

The "INSERT where does not already exist" should work as-above

sql_dan
Starting Member

43 Posts

Posted - 2010-07-12 : 10:35:32

Thanks Kirsten got it working thanks to your code.

Running a little expensively as the source table needed to be created as a temp table but i'm happy it works as it only needs to run once a day and this can be done as an overnight job!

Thanks for your help

Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources