We help IT Professionals succeed at work.
Get Started

Insert/Update Values From One (Temp) Table To Another In SQL Server 2008

yetti78
yetti78 asked
on
776 Views
Last Modified: 2012-05-09
Okay, I have a two temp tables inside a stored procedure.

1. A temp table that contains rows of log entries that are populated directly from a logging table in the database. There can be many entries inside for a single asset, as a user may have created an asset, then edited it, then edited it a second time. This would be represented as three separate rows in the database and is reflected that way in the temp table. There is a datestamp field on the table in the database and I populate the temp table from the database table sorted by datestamp in ascending order.

2. A temp table where I want to store one row for every item that was in the log files, and have the action type set to whatever the most recent entry in the temp table above has for that particular item.

SQL code below creates the objects I am working with.

What type of SQL is used to take the data from the #logEntries temp table, and either:
1. If there is no row already in #TARGET for that itemID and itemTypeID, insert a new row into #TARGET with that logEntryTypeID.
2. If there is a row already in #TARGET for that itemID and itemTypeID, update the logEntryTypeID in the row stored in #TARGET to the logEntryTypeID of that row from #logEntries

----------
End up with two rows in #TARGET
'1/3/2010', 1, 1, 3
'1/1/2010', 2, 1, 3

----------

Notes
------------------
1. Again the #logEntries table is sorted by datestamp based on the insert. This should produce the desired result in the #TARGET table of having one row per item, with the most recent actionType being the one listed.

2. I tried to MERGE but that didn't work as the rows don't exist already in the #TARGET table, thus all four rows are brought over instead of just the two
-- Database table for storing log entries
CREATE TABLE logEntries (
logEntryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
datestamp SMALLDATETIME NOT NULL, 
itemID INT NOT NULL,
itemType INT NOT NULL,
logEntryTypeID INT NOT NULL -- 1 = create, 2 = delete, 3 = update
)
GO

--Insert sample records into log table in database
INSERT INTO logEntries(datestamp, itemID, itemType, logEntryTypeID)
VALUES
('1/1/2010', 1, 1, 1), -- Create a new item
('1/2/2010', 1, 1, 3), -- Update the item
('1/3/2010', 1, 1, 3), -- Update the item again
('1/1/2010', 2, 1, 3) -- Update the item
GO

-- Temp table to hold log entries for stored procedure
CREATE TABLE #logEntries (
logEntryID INT NOT NULL, 
datestamp SMALLDATETIME NOT NULL, 
itemID INT NOT NULL,
itemType INT NOT NULL,
logEntryTypeID INT NOT NULL
)
GO

-- Copy rows from log table in database into log temp table
INSERT INTO #logEntries
SELECT	logEntryID,
		datestamp,
		itemID,
		itemType,
		logEntryTypeID
FROM	logEntries 
ORDER BY datestamp ASC
GO

-- Temp table to hold log entries for stored procedure
CREATE TABLE #TARGET ( 
itemID INT NOT NULL,
itemType INT NOT NULL,
logEntryTypeID INT NOT NULL
)
GO

/*
NEW BUSINESS LOGIC HERE
*/

SELECT * FROM #TARGET

DROP TABLE logEntries
DROP TABLE #logEntries
DROP TABLE #TARGET
GO

Open in new window

Comment
Watch Question
Topic Advisor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE