hoyaabanks
asked on
Inserting and removing a group of records
I am trying to insert a selection of records into a table and then remove them from their current table. Finally I would like to return a list of what those records were for a report.
I have no idea how to do this. I know it is something simple like:
@ReturnVar = table
SELECT * into @ReturnVar
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
INSERT (
SELECT *
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
)
INTO devRemovedCert
DELETE (
SELECT *
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
)
FROM devRemovedCert
return @ReturnVar;
I have no idea how to do this. I know it is something simple like:
@ReturnVar = table
SELECT * into @ReturnVar
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
INSERT (
SELECT *
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
)
INTO devRemovedCert
DELETE (
SELECT *
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
ORDER BY u.eMail
)
FROM devRemovedCert
return @ReturnVar;
Last Comment
chapmandew
are you using 2005?
You could do this the way you suggested, using a temp table, but this is not really needed. If you do not do this using a temp table you should first insert the selected records and than use this table as a reference for the delete and reporting. Otherwise it's possible that records get inserted after the selection and before the delete and thus actually get lost.
You could also use 'begin transaction', select the records 'with lock', do the insert into the other table, and do the delete. You should set the transaction isolation level to repeatable read using 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ' to be sure.
You could also use 'begin transaction', select the records 'with lock', do the insert into the other table, and do the delete. You should set the transaction isolation level to repeatable read using 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ' to be sure.
maybe something like this:
SELECT * into #TempTable
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
INSERT INTO DevRemovedCert
SELECT * FROM #TempTable
DELETE d
FROM devIssuedCert d
JOIN #tempTable t ON d.UserProfileID = t.UserProfileID
select * from #TempTable
SELECT * into #TempTable
FROM devIssuedCert i, devUserProfile u
WHERE i.UserProfile_ID = u.UserProfile_ID
AND u.eMail = ''
INSERT INTO DevRemovedCert
SELECT * FROM #TempTable
DELETE d
FROM devIssuedCert d
JOIN #tempTable t ON d.UserProfileID = t.UserProfileID
select * from #TempTable
ASKER
SELECT IssuedCert.IssuedCertID, IssuedCert.UserProfile_ID,
IssuedCert.CertNumber into #TempTable4
FROM IssuedCert LEFT OUTER JOIN
UserProfile ON IssuedCert.UserProfile_ID = UserProfile.UserProfile_ID
WHERE (UserProfile.eMail = '')
INSERT INTO RemovedCert
SELECT * FROM #TempTable4
SELECT * FROM RemovedCert
I am just trying to get the insert to work, but I get an error. It selects everything into the temp table fine.
How do I destrory the TempTable at teh end of the Script?
How do I get the insert to work? I get this error:
Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table 'RemovedCert' can only be specified when a column list is used and IDENTITY_INSERT is ON.
FInally, I only want to delete from IssuedCert at the end. I only want to delete the records that I have selected above, the ones without email addresses. Why won't something like this work:
DELETE FROM IssuedCert WHERE IssuedCert_ID IN (SELECT IssuedCert_ID FROM #TempTable4)
IssuedCert.CertNumber into #TempTable4
FROM IssuedCert LEFT OUTER JOIN
UserProfile ON IssuedCert.UserProfile_ID = UserProfile.UserProfile_ID
WHERE (UserProfile.eMail = '')
INSERT INTO RemovedCert
SELECT * FROM #TempTable4
SELECT * FROM RemovedCert
I am just trying to get the insert to work, but I get an error. It selects everything into the temp table fine.
How do I destrory the TempTable at teh end of the Script?
How do I get the insert to work? I get this error:
Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table 'RemovedCert' can only be specified when a column list is used and IDENTITY_INSERT is ON.
FInally, I only want to delete from IssuedCert at the end. I only want to delete the records that I have selected above, the ones without email addresses. Why won't something like this work:
DELETE FROM IssuedCert WHERE IssuedCert_ID IN (SELECT IssuedCert_ID FROM #TempTable4)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
