DELETE TOP

Environment: SQL Server 2005
Language: T-SQL

I want to insert the top first record from the temp table '#NewTemp'
to the Employee table 1 record at a time until all the records from the temp table
have been copied.
After inserting the top first record into the Employee table, how can I retrive the identity key 'NewEmployeeID' from the Employee table?
I would then like to delete the top row in the #NewTemp table.
The process should be repeated until the @.Counter variable is 0.
Currently, I get an error msg 'Incorrect syntax near the keyword TOP'.What's wrong with the following T-SQL and how can I fix it? thanks.

BEGIN
DECLARE @.Counter as INT
SELECT @.Counter = COUNT(*) FROM #NewTemp
DECLARE @.TopCount AS INT
SET @.TopCount = 1

WHILE @.Counter > 0
BEGIN
INSERT INTO dbo.Employee(
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail)
SELECT TOP(@.TopCount)
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail
FROM dbo.#NewTemp

SELECT SCOPE_IDENTITY() AS NewEmployeeID

PRINT 'The count is ' + CONVERT (VARCHAR (10), @.Counter)

PRINT --NewEmployeeID

SET @.Counter = @.Counter -1

DELETE TOP(@.TopCount) from dbo.#NewTemp
END

DROP TABLE dbo.#NewTemp
END

I'm sure you have your reasons, but I have to say, this is very, very 'odd' indeed.

DELETE does NOT recognize TOP. You cannot [ DELETE TOP ]. In fact, if users are active in your database, a second query using TOP may not return the same row(s) as the first query. In your SELECT query, TOP without a WHERE criteria just doesn't make any sense.

Why go to so much trouble?

Let us know what you are attempting to accomplish and perhaps we can help you.

|||

What I'm trying to accomplish is this.

Copy each row from temp table to the Employee table. After inserting each record into the Employee table,

I would like to get the Identity key for the new record inserted from the Employee table. Thanks.

|||

You will need to use the new OUTPUT clause of the input statement. You cannot use either @.@.identity or scope_identity() when you are inserting multiple rows. Just a second and I will try to get you an example. Give a look to this article:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You ought to be able to do something like this to retrieve your list of identities:

create table #identityList (idKey integer)

insert into targetTable
select col1,
col2,
...
colN
output inserted.theIdentityColumn
into #identityList
from yourTempTable

-- to retrieve the identity values:
select idKey

from #identityList

Now, if you are going to need to match your identies back to the temp table that you inserted from you will also need to include column(s) in your identity list that make the inserted row unique.

|||

thanks, nice article. I think it will help. Is 'inserted' a reserved word in the code above?

In my code above, reason I have while loop and a counter is so I can go each record at time for insertion from the

temp table to the Employee table. What would be the alternative if using scope_identity() or @.@.identity?

|||

John,

In my earlier response, I asked if you would give us more detail information about what you are attempting to accomplish. It is not at all clear what is going on here.

If you only wanted to move the data from the #NewTemp table into the Employees table, it is as simple as an INSERT... SELECT.

Code Snippet

INSERT INTO dbo.Employee
( EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
)
SELECT
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
FROM dbo.#NewTemp

However, perhaps there is some thing you still haven't told us. What is your concern about the SCOPE_IDENTITY() or @.@.IDENTITY?

We can't do a good job of helping you unless you give us all of the information.

|||

create table dbo.#newTemp
( EmployeeFName varchar(20),
EmployeeLName varchar(20),
EmployeePhone varchar(14),
EmployeeEmail varchar(30)
)
insert into dbo.#newTemp
select 'Dave', 'Mugambo', '(800)555-1212', null union all
select 'Kent', 'Waldrop', '(800)555-1212', null union all
select 'Geoff', 'Waldmeyer', '(800)555-1212', null
select * from dbo.#newTemp
/*
EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- --
Dave Mugambo (800)555-1212 NULL
Kent Waldrop (800)555-1212 NULL
Geoff Waldmeyer (800)555-1212 NULL
*/

declare @.idList table (newEmployeeID int)

insert into dbo.Employee
output inserted.employeeId
into @.idList
select EmployeeFName,
EmployeeLname,
EmployeePhone,
EmployeeEmail
from dbo.#newTemp

-- --
-- If you need to see the new employee
-- information, you can you the
-- permanent table to the IDList like
-- this:
-- --

select EmployeeId,
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
from dbo.employee a
inner join @.idList b
on a.employeeId = b.newEmployeeId

/*
EmployeeId EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- -
1 Dave Mugambo (800)555-1212 NULL
2 Kent Waldrop (800)555-1212 NULL
3 Geoff Waldmeyer (800)555-1212 NULL
*/

go

drop table dbo.#newTemp

(edited the syntax; it was previously wrong)

|||

After filling the table variable with the inserted records,

how can I get each newEmployeeId one at a time. I would like to

pass this newEmployeeID to another sp one at a time.

thanks much.

|||

If this is what you must do, then use a cursor with a while loop -- but understand, cursors tend to be performance killers. A better approach is to design set based operations; it is what SQL server is best at. I will edit this with some posts that talk about the problems of cursors.

Here are some posts that comment on cursors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

I have many more examples in my list.

|||

If you really MUST do a row-wise operation, instead of a CURSOR, I recommend using the previous suggestion of the SQL 2005 OUTPUT keyword, outputing into a table variable, then adding an IDENTITY column to the table variable, and then using a WHILE loop to step through the rows in the table variable.

It will not be as resource intensive on the server, and most likely even execute faster than a CURSOR.

|||

As an aside, delete does recognize TOP in 2005. It is non ordered, so it doesn't make sense in this particular case. It's use is to batch deletes, like if you want to delete a large number of rows, but want to limit the pressure on the log, you can just delete them in batches over and over until all data is gone.

Code Snippet

create table #NewTemp
(
EmployeeFName varchar(30)
, EmployeeLName varchar(30)
, EmployeePhone varchar(30)
, EmployeeEmail varchar(30)
)

insert into #newTemp
select 'fred','flintstone','quarry 3424','fredf@.slate.com'
union
select 'barney','rubble','quarry 3452','barneyr@.slate.com'

delete top (1) from #newTemp

select *
from #newTemp