Insert/Update Values From One (Temp) Table To Another In SQL Server 2008
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
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
ASKER
anonymous,
Read the entire description. Inserts into tables using selects is not the issue. Inserts into tables using selects and distincts is not the issue. Inserts into tables if the row is not there, and updating that row if it already is, that's the issue.
Read the entire description. Inserts into tables using selects is not the issue. Inserts into tables using selects and distincts is not the issue. Inserts into tables if the row is not there, and updating that row if it already is, that's the issue.
try this..
-- 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
*/
IF NOT EXISTS (SELECT logEntryID FROM #logEntries where itemID=@itemID AND itemType=@itemType)
BEGIN
INSERT INTO #TARGET
SELECT itemID, itemType, logEntryID
FROM #logEntries
END
ELSE
BEGIN
UPDATE #TARGET
SET logEntryID= (SELECT logEntryID FROM #logEntries where itemID=@itemID AND itemType=@itemType)
WHERE itemID=@itemID AND itemType=@itemType
END
SELECT * FROM #TARGET
DROP TABLE logEntries
DROP TABLE #logEntries
DROP TABLE #TARGET
GO
ASKER
rushShah,
Looks good except it it appears you are expecting passed in variables of @itemID and @itemType.
The stored procedure doesn't take these, instead it needs to
1. Check each row in the #logEntries temp table and see if there is a row in the #TARGET for #logEntries.itemID and #logEntries.itemType
2. If there is a row in #TARGET, update the the logEntryID field for row in #TARGET with the logEntryID value from #logEntries
3. If there is not a row in #TARGET, insert a row.
Again, the SP needs to run a check for every row in the #logEntries and see if a row exists in #TARGET for it. I have looked into conditional INSERT/UPDATE statements and there are no clear examples on how to do them based on multiple rows in a source table. Hope that makes the business case clearer.
Looks good except it it appears you are expecting passed in variables of @itemID and @itemType.
The stored procedure doesn't take these, instead it needs to
1. Check each row in the #logEntries temp table and see if there is a row in the #TARGET for #logEntries.itemID and #logEntries.itemType
2. If there is a row in #TARGET, update the the logEntryID field for row in #TARGET with the logEntryID value from #logEntries
3. If there is not a row in #TARGET, insert a row.
Again, the SP needs to run a check for every row in the #logEntries and see if a row exists in #TARGET for it. I have looked into conditional INSERT/UPDATE statements and there are no clear examples on how to do them based on multiple rows in a source table. Hope that makes the business case clearer.
ok..try this..
-- 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
*/
DECLARE @itemID INT
DECLARE @itemType INT
DECLARE item_cursor CURSOR FOR
SELECT itemID, itemType
FROM #logEntries
OPEN item_cursor
FETCH NEXT
FROM item_cursor INTO @itemID, @itemType
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT logEntryID FROM #logEntries where itemID=@itemID AND itemType=@itemType)
BEGIN
INSERT INTO #TARGET
SELECT itemID, itemType, logEntryID
FROM #logEntries
END
ELSE
BEGIN
UPDATE #TARGET
SET logEntryID= (SELECT logEntryID FROM #logEntries where itemID=@itemID AND itemType=@itemType)
WHERE itemID=@itemID AND itemType=@itemType
END
FETCH NEXT
FROM item_cursor INTO @itemID, @itemType
END
CLOSE item_cursor
DEALLOCATE item_cursor
SELECT * FROM #TARGET
DROP TABLE logEntries
DROP TABLE #logEntries
DROP TABLE #TARGET
GO
hi angelIII,
I provided solution which author yetti78 wants..
but after that author didn't give any updates..
I provided solution which author yetti78 wants..
but after that author didn't give any updates..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I didn't read the entire description but I know you can insert data from one table to another by combining INSERT INTO + SELECT. Have a look at the below link under the "Inserting data into a table" section:
http://www.readylines.com/sql-one-liners-examples-commands
Hope that helps.
anonymous expert