Insert, Update queries

Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?

I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.No graphical way. Lots of people use Access exactly like you mentioned. Another way is with Query Analyzer. Right click the table in the object browser and you'll have options for scripting INSERTS, UPDATES, DELETES, etc. Not graphical, but handy to eliminate some typing and spelling mistakes.|||Thanks for the reply!

You know, in many ways Access is superior to SQL Server. Easy interface for designing and building any types of queries and forms, easy to link tables to any form of database (Oracle, SQL Server, DBF), Great reporting tool, cheap, and so on and so forth...

if only it was more stable and faster for use in a larger corporate setting with many users hitting it constantly, it would be my #1 choice for database development.|||Access gets some bad press, but it's good at what it's meant to be. Doesn't hold a candle to SQL Server for what it's not meant to be. Just a case of the right tool for the job.

insert statement problem

Hi all,

I'm trying to calculate the data from one table and insert the results into another table. The queries are the following:

1. select top 5 userId, count(photoId) numOfPhoto from photo group by userId order by count(photoId) desc

2. select top 5 userId, count(photoId) numOfPhoto from photo where datesubmitted > DATEADD(dd,-30,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) and datesubmitted < DATEADD(dd,-8,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))group by userId order by count(photoId) desc

3. select top 5 userId, count(photoId) numOfPhoto from photo where datesubmitted > DATEADD(dd,-7,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) and datesubmitted < DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) group by userId order by count(photoId) desc

The another table's structure is the following:

id, overall, overallNum, monthly, monthlyNum, weekly, weeklyNum

the first query's result will go to overall and overallNum, the second query's result will go to monthly and monthlyNum, the third query's result will go to weekly and weeklyNum

Sorry about my bad English. Is this possible, shall I use something like table valued function?

Thanks a lot,

Jason

I got error with following code:

Msg 170, Level 15, State 1, Procedure all_submitter, Line 10

Line 10: Incorrect syntax near '@.oNum'.

Anybody know why I'm getting that? Here is my code: thank you..

create procedure [dbo].[all_submitter]

as

set nocount on

DECLARE @.oId int, @.oNum int

declare sCursor Cursor

for select top 5 userId as oId, count(photoId) as @.oNum from photo group by userId order by count(photoId) desc

open sCursor

fetch sCursor into @.oId,

@.oNum

while (@.@.Fetch_Status = 0)

begin

insert into TopSubmitter (overallTopSubmitterId, overallTopSubmitterSubmittals) values (@.oId, @.oNum)

fetch sCursor into @.oId,

@.oNum

end

close submitterCursor

deallocate submitterCursor

return

|||

You can't do this:

count(photoId) as @.oNum

Change to

select top 5 userId as oId, count(photoId) as oNum from photo group by userId order by count(photoId) desc

and it will work. Unless I am missing something, you should be able to do:

insert into TopSubmitter (overallTopSubmitterId, overallTopSubmitterSubmittals)
select top 5 userId as oId, count(photoId) as oNum
from photo
group by userId
order by count(photoId) desc

Louis

|||thanks louis, what a stupid mistake!|||Nah, it is the kind of thing that you can't see when you are the one programming it, but when you look over someone else's work it announces itself :)

INSERT Query, Guid AutoIncrement Help

Im still learning my way around SQL and queries and i was wondering :

How do you get a SQL Table to autoincrement a Guid? (is it "Is Identity?" or "RowGuid"...)

How would i create a new row with a new Guid, and insert into the values i want without specifying the Guid?

You would need to use NewID() to get the next random GUID.

INSERT INTO yourTable (col1, col2,...) VALUES (@.val1, NewID(), @.val3,...)