Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating a table from a view

Posted on 2008-06-21
5
Medium Priority
?
235 Views
Last Modified: 2010-04-21
Hi Experts:
I've created a view in SQL2000.  This view is created using 2 tables and another view.  Now I need to create a table from the results of this view so that I can export the results in txt comma deliminated format.  This is the code I have so far:

SELECT qry_Project_Request_HE_001.*, DATEDIFF(M,(SUBSTRING([AADOB],5,2) + '/' + Right([AADOB],2) + '/' + Left([AADOB],4)),
       (SUBSTRING([BEG_DATE_SERV],5,2) + '/' + Right([BEG_DATE_SERV],2) + '/' + Left([BEG_DATE_SERV],4)))/365.25 AS Age_DOS,
        ABPLAN AS Aid_Code, ABIPA AS IPA, 'County' = CASE
           WHEN ABGRUP like 'Riv%' THEN 33
           WHEN ABGRUP <> 'Riv%' THEN 36 END, abgrup, AASEX AS Gender, qry_Project_Request_HE_001.Beg_Date_Serv
FROM IEHPSQL2.Diam_725.diamond.JMEMBRM0_DAT JMEMBRM0_DAT_1 INNER JOIN
                      IEHPSQL2.Diam_725.diamond.JMELIGM0_DAT JMELIGM0_DAT_1 ON JMEMBRM0_DAT_1.AASUBNO = JMELIGM0_DAT_1.ABSUBNO AND
                      JMEMBRM0_DAT_1.AAPERSNO = JMELIGM0_DAT_1.ABPERSNO INNER JOIN
                      dbo.Qry_Project_Request_HE_001 ON
                      JMEMBRM0_DAT_1.AASUBNO COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Project_Request_HE_001.SubID AND JMEMBRM0_DAT_1.AAPERSNO COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Project_Request_HE_001.Pers_no
WHERE (qry_Project_Request_HE_001.Beg_Date_Serv)>=[ABEFFDT] And (qry_Project_Request_HE_001.Beg_Date_Serv)<=[ABTERMDT] OR
      (qry_Project_Request_HE_001.Beg_Date_Serv)>=[ABEFFDT] And (qry_Project_Request_HE_001.Beg_Date_Serv)<'0'

I can't figure out how to create the new table without having to create it manually for all the fields.

Thanks in advance.
0
Comment
Question by:IEHP1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21838349
here we go:
SELECT * 
  INTO your_new_table
 FROM your_view

Open in new window

0
 

Author Comment

by:IEHP1
ID: 21838394
Hi angellll and thanks for the response.  

My issue is I created the view in SQL analyzer and can't save it as a view because of the case statement.  And will your solution above create a brand new table?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 21838402
>My issue is I created the view in SQL analyzer and can't save it as a view because of the case statement.  
you are using the view/query designer?
forget that "tool", it indeed cannot handle the CASE statement, run the CREATE VIEW statement as a normal query.

note: in 95% of the view I create on my databases there is at least 1 case statement, hence the query designer is useless to me.
0
 

Author Closing Comment

by:IEHP1
ID: 31469456
It worked.  Thank you VERY much
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21838420
you are welcome
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question