Creating a job to insert a record in a table about other records 5

[POSAPAY]

Hi,

I've created a job in Jobs on our SQL server, but not sure what I need to put in the Step's Command area.


I need to check TableA where EmailTo is same as Email in TableB and add a single record in TableC and TableD with a summary of the records found in TableA.

TableA has MsgID, EmailFrom, EmailTo, Subject, Date
TableB has Email
TableC has MsgID, Message
TableD has MsgID, EmailFrom, EmailTo, Subject, Date

For example, if TableB has two records: tom@smith.com and bill@smith.com
For each I'd like to scan TableA for last 24 hours of records, and take the EmailFrom, EmailTo, Subject, Date from TableA and put in a variable say Message as one blob of text.

Then take that Message and save it to TableC, and get the MsgID from TableC and save the MsgID, a hard-set EmailFrom, and EmailTo being same as TableB's Email, with a hard set Subject.

Basically a report in one record run once a day for each user's Email in TableB.

I'm just not sure how to compile that and where to put it.

 

[POSAPAY]

markros,

Yeah, I was just thinking that I haven't seen such a long thread ever before here. But I think my questions are pretty close to being all answered now.

In your record count suggestion, I guess my curiosity is about the second part:

select @RecCount = count(*) from (select my Complex select) X


Wouldn't the count(*) depend on the columns selected in the "select my Complex select"?

And basically isn't it returning the data of those columns?

Isn't there a way to have it simply return a record count without passing data? I realize this wouldn't print the data, but it is still pulling it from the database,... isn't it?

 

[Emtucifor]

Definitely, Count() is better.

If you do a whole select just to get the @@Rowcount after, you're actually pulling all the data from the database and creating a spurious rowset for no reason.

Doing Count() will return only a single scalar value.

You can settle questions like this in the future for yourself in two ways:

1. View the execution plan of the two queries.
2. (the only ironclad way to see how much work the server is doing) run a trace in SQL Profiler to see what is happening.

 

[JeffModen]

Ah... "It Depends" on the rowcount thing. If you're already getting the data for something else anyway, then why do an extra query? Just use @@RROWCOUNT in that case. Otherwise, I agree... the COUNT(*) thing will be fine.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[POSAPAY]

Emtucifor, Thanks! I'll need to get familiar with SQL Profiler. Any pointers on where I can start with that? (I'll start digging online google&MS)

JeffModen, Thanks! Yeah in this case I needed the rowcount for all records, while only needing data for the top 10.


I think I'm all set now. Thanks to everyone on this thread, not only did I accomplish my goal, I learned a lot about SQL, limitations, version difference issues, syntax and how a thread can possibly become this long.

 

[Emtucifor]

SQL Profiler is a normal part of the SQL Client install. Just search your start menu for it. Do be aware that profiler consumes server resources when it's running so be sure you understand the implications of this before you run against production databases. You don't want to be to blame for hurting performance for other users...

Profiler is an invaluable part of a SQL query writer's toolbox. It isn't the be-all and end-all, but it's very useful sometimes (especially when debugging client applications to see what actual script is being submitted to the database).

 

[markros]

If your SQL Server version is not Express, you can run Profiler from Tools menu in SSMS.

 

[Emtucifor]

markros, I forgot about that! I like to run them separately so they stay in separate processes. A crash in one can take out the others otherwise. I've had weird things happen.

 

[JeffModen]

Thanks for all the feedback, Posapay.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

The fix is to the Odsole70.dll file. The first fixed version of the binary is 2005.90.2232.0.

That's some good info... thanks!



--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row