CODEWITHSUNDEEP
sqlsql-server
user2225470
user2225470

Reputation: 41

creating new table from select statement

I have a select query that I am using in Access to fetch some particular data that I want to achieve. Now I am moving to SQL Server (creating ssrs report) so I want to take this query from Access and use it in SQL Server and create a new table every time when I execute this query.

Here is my current query that I use in Access db:

select taxid, address1, count(address1)
from dbo.tblaccounts
group by taxid, address1
order by address1 asc, count(address1) desc;

Where should I insert/into statement...or is that something else that I need here.

Upvotes: 1

Views: 16047

Answers (4)

user2225470
user2225470

Reputation: 41

Create Procedure proc_new_table 
As
SET NOCOUNT ON;

IF OBJECT_ID('new_table','U') IS NOT NULL 
DROP TABLE new_table;
select distinct cust_name, taxid
INTO new_table
from tblaccounts;

Thank you all!

Upvotes: 0

Steve Lovell
Steve Lovell

Reputation: 2564

I've got a hunch that what you really want to do is create a view. Queries in MS Access are like views (or in the case of ADD/DELETE/UPDATE queries they are like stored procedures). Just like MS Access queries, a view can be referenced by other code in the same way as a table and like an Access Query this acts like an "always up-to-date" table.

create view ViewName
as
select
    taxid,
    address1,
    count(address1) as address_count
from
    dbo.tblaccounts
group by
    taxid,
    address1

You'll notice that I've left off the ORDER BY clause. They aren't permitted here, you specify the order by in whatever code selects from this view.

Upvotes: 1

LONG
LONG

Reputation: 4610

If you need to create a new table based on that query, there are multiple ways, the easiest way is use Select Into From, which the query will be:

select taxid, address1, count(address1)
INTO TABLEA
from dbo.tblaccounts
group by taxid, address1
order by address1 asc, count(address1) desc;

Note: this method only transfer the data, and constraints,etc. will not get preserved. BUT if you need to get that table every time you run the query, you need to DROP and create the table again, otherwise, there will throw the object error. Also, there might be lots of approaches. The simple way to do is:

IF EXISTS(select 1 from sys.objects where name = 'TABLEA' and type = 'U') --or you could use IF EXISTS(OBJECTID('TABLEA','U'))
BEGIN 
DROP TABLE TABLEA
END
--then paste your query after
    select taxid, address1, count(address1)
    INTO TABLEA
    from dbo.tblaccounts
    group by taxid, address1
    order by address1 asc, count(address1) desc;

Upvotes: 1

user6373040
user6373040

Reputation:

What you are trying to perform is called Select Into or Create Table As Select (CTAS). MSDN has a great discussion of when to use CTAS vs. Select Into (https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-ctas). Though this article discusses data warehousing, the discussion is valid in this case, IMO.

As most of my table creation from another table involve both complex joins and unions, I tend to use CTAS. That said, the Select Into method will likely work in your scenario.

The code you would use is either:

CREATE TABLE AccountSummaryOrWhatever AS
select taxid, address1, count(address1)
from dbo.tblaccounts
group by taxid, address1;

Or

select taxid, address1, count(address1)
INTO AccountSummaryOrWhatever 
from dbo.tblaccounts
group by taxid, address1;

Upvotes: 4

Related Questions