Insert Multiple Records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Insert Multiple Records

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2012-10-17 : 11:04:57

Hi,

I have a registration system and am trying to write a sql that will run nightly to insert all new members that registered the prior day.

Members Table
-----------------
FirstName
LastName
...
IsNewFlag (initially set to true)

Member Process Table
--------------------
Here I need to insert records for all rows in the Members Table where the IsNewFlag is set to true.

Then, upon success set the Member Table.IsNewFlag = False.

Trying to avoid cursors.

Thanks,
Mevad

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-17 : 12:07:18

The code should be easy enough:[CODE]begin tran

insert into MemberProcess (FirstName, LastName) -- Add other column names if they exist and are relevant
select FirstName, LastName
from Members
where IsNewFlag = 1

update Members
set IsNewFlag = 0

commit tran[/CODE]This could probably be made more efficient if you could limit the processing to the "prior day" data but you haven't listed any date column. Once you have the code operational, you can put it into a SQL Job that is scheduled to run nightly.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

MevaD
Starting Member

32 Posts

Posted - 2012-10-17 : 12:20:10

Thanks Bustaz. I have date fields in the real tables so I can take your advice to limit the processing by date or I guess just look for IsNewFlag = 1 since the flag will be updated after processing. The insert looks easy enough, In my head I was making it much harder than it had to be. It happens... Thanks again.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-10-18 : 07:31:15

With SQL2008, and above, you may be able to avoid the explicit transaction by using OUTPUT INTO.
eg.


UPDATE Members
SET IsNewFlag = 0
OUTPUT inserted.FirstName, inserted.LastName
INTO MemberProcess
WHERE IsNewFlag = 1;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-18 : 11:54:54

Ifor,

Do you have any thoughts on how the performance compares using the single statement versus the explicit transaction? I would think that the single statement should do better I don't have any empirical data to point to.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources