Techies, database advice SQL Studio

iPete

So your a knowledgeable bunch!

I'm competent with Microsoft Access and the basics but I've set myself the task of shifting our database onto SQL Server and have a proper back end.

I've got SQL Studio 2005 but can't get my head around Queries. In Access you set up a query and see the results in a table as an object in your database, nice and easy.

How on earth do Queries run in SQL Studio? I've written one to Select data from another database and can see that it works in the Result window but no idea how to get the results to populate a table in my new one! Assume they don't work in the same way as Access like objects?

notsoblue

You can create queries by connecting to the db in question, then File -> New -> Query with current connection. (Or Ctrl N). Pop the query in there, parse it (ctrl-F5) and run it (F5).

Does that answer your question?

optimisticbiker

NSB, not sure that answers OP's question. iPete, if i understood you right you want to populate a table in sqlserver database from data in the access db?

Firstly, there is a migration tool which will build a SQL server db from an access db. Have you looked at this?

Secondly, if you want to hand roll this you need to write a query that moves the data... having created the table in sql server in your target database and having an open connection to both source and target you need to do something like:

insert into  targetDB.target_table ("targetcolumnnameA", "targetcolumnnameB",...)
 select "sourcecolumnnameA", "sourcecolumnnameB", ....  
 from sourceDB.source_table

tgotb

Am I right in inferring that you want to keep the Access/VBA front end, but migrate the data storage to SQL Server?

pst88

If you want a query that you can treat as if it were a table you might want to create a view? It depends what you're trying to do really.

EKE_38BPM

Turn it off and turn it on again?

Or if you're feeling nostalgic, try SYS64738. Free water bottle to whoever remembers that.

iPete

Thanks all, I've actually made the migration, that process is fairly straight forward.

I'm just trying to get my head around Queries in this new software. In more details:

I have two database on the server. I want to query two two tables from database 1 and store the results in a new table in database 2. Think I may need to combine an INSERT and SELECT statement?

I think pst88 is onto it, create a 'View' but I'm not sure what this is yet! In Access you see a query like a table object, is there an equivalent in Studio, a colleague mentioned Views but is also in same state of confusion as me, so this could be it!

Going to look up some youtube tutorials after dinner on 'Views'.

apreading

A view is probably the closest thing to a select query in Access, but you dont want to use views for everyday stuff.

Optimistic has give you what you want - an INSERT INTO tblNEW SELECT FROM tblOriginal statement.

It would be a waste to write a view for every time you want to do a select - you would end up with hundreds or thousands of them...

optimisticbiker

A View is useful where you have a very complex parameterised query that generates a result set from a join across several tables and you want to hide the complexity from the end user. You wouldn't generally use a View to do this sort of thing.

What I showed before but the second bit is a join. For example....

INSERT INTO target (a,b,c,....)
SELECT s1.a, s1.b, s2.c,....
FROM s1 source1, s2 source2
WHERE s1.id = s2.id

CiB

So you have two tables, and you want to be able to see the content of both from a single query as a snapshot of the data whenever the query is fired off, or do you want to d a one-shot copy of the data in these two tables into one new table and then view that?

If the former, best to create a stored procedure that contains the query to return the relevant columns from the two tables with any filtering, sorting & grouping included. It'll run more quickly and be easier to call from your client assuming that's how you run your queries.

Your query will be along the lines of

Select A.ColumnA, A.ColumnB, B.Column4, B.Column7
From DB1.DBO.Table1 A
Inner Join DB2.DBO.Table2 B on A.KeyCol = B.KeyCol
Where A.KeyCol = 'HORSE'
Order By B.Column7

If it's a one-shot move of data OptimisticBiker has it pretty much covered.

CiB

Stepping back a bit, your comparison with Access queries being saved as objects is similar to how Stored Procs work; the query is created & saved as an object in the DB (under Programmability --> Stored Procedures) and to run it r-click --> execute instead of double-click. It'll prompt for any parameters then return the result in the query edit window, almost like Access.

Or you can save the query as a text file (.SQL ext) wherever you want in the filesystem and open it / drag it into the edit window & hit F5 to run it that way.

sketchley

There's also a query builder you can use similar to access to build the sql and create the joins graphically if you don't want to write SQL...

iPete

Thanks, some very useful stuff to work on tomorrow!

Think I need something along the lines of a snapshot. I have Database 1 that other staff can't access the data for and want to use several fields in lookups in other tables in Database 2. Number 1 is a mirror so I can't make any changes or add tables etc. so need to run the query in Database 2 and store/copy the data there.

I think CiB and Optimistic have that covered and I'll see how I can apply your examples and learn a bit about creating stored procedures. Thanks!