Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

SQL Statement to SELECT and CREATE


  • Please log in to reply
21 replies to this topic

#1 mybuddyeric

mybuddyeric

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 10:52 AM

Hello!

I'm trying to write a query in MS Access 2003 to do the following:

SELECT three columns from existing table
CREATE a new table with 3 columns
ADD PRIMARY KEY to first column

Being new to SQL, I can't seem to get the syntax correct. Here's what I have so far:
SELECT SQLUser_MEMBER_ACCT.acn, SQLUser_MEMBER_ACCT.acn_Status, SQLUser_MEMBER_ACCT.mbr_nam INTO f_members
FROM SQLUser_MEMBER_ACCT
CREATE TABLE f_members2
(
acn int NOT NULL PRIMARY KEY,
acn_Status varchar(255) NOT NULL,
mbr_nam varchar(255) );

If there are any SQL experts out there, I sure could use 2 minutes of your time! Thank you so much!
fueled by coffee™

BC AdBot (Login to Remove)

 


#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:05:55 AM

Posted 09 November 2011 - 12:57 PM

Not sure what it is in access, but in standardized sql, it is something like this:
CREATE TABLE Foo SELECT * FROM Bar;
You will need to figure out how to alter it to fit within your constraints.

#3 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 01:06 PM

Well, that's honestly my question - what constraints should I use and in which order? MS Access understands standard SQL from what I've seen.
The sample I've provided in my code is a combined version of previous queries that have worked. For some reason the compiler (or whatever it is Access uses) takes issue with the "CREATE TABLE" portion citing a syntax error.

Would anyone else have any ideas?

Not sure what it is in access, but in standardized sql, it is something like this:
CREATE TABLE Foo SELECT * FROM Bar;
You will need to figure out how to alter it to fit within your constraints.


fueled by coffee™

#4 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:05:55 AM

Posted 09 November 2011 - 01:38 PM

Look at the order of operations in your query, and the order of operations in my query. I can't possibly guess the error that you are getting, but if your order of operations is incorrect, the compiler is going to indicate an error in that position.
http://www.techonthenet.com/sql/tables/create_table2.php

Also, when you create a table from another table, the newly created table will have the same characteristics of the table from which it is getting its information. You are making this harder than it really is. :)

#5 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 01:49 PM

I think the issue here is that the table I'm pulling from does NOT have a defined primary key. In all honesty, I could probably just write another macro to run alongside the SQL query to add the primary key after the query updates. I was just hoping to avoid making this a piece-mill operation.

Oddly enough, I ran across your website before coming here to post my question. Your post makes sense and I understand the logic - it just didn't address my primary key question. Never wanted to make things hard. :huh:

Look at the order of operations in your query, and the order of operations in my query. I can't possibly guess the error that you are getting, but if your order of operations is incorrect, the compiler is going to indicate an error in that position.
http://www.techonthenet.com/sql/tables/create_table2.php

Also, when you create a table from another table, the newly created table will have the same characteristics of the table from which it is getting its information. You are making this harder than it really is. :)


fueled by coffee™

#6 dev00790

dev00790

    Bleeping Chocoholic


  • Members
  • 5,037 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:11:55 AM

Posted 09 November 2011 - 02:24 PM

Hi

SELECT three columns from existing table
CREATE a new table with 3 columns
ADD PRIMARY KEY to first column

Being new to SQL, I can't seem to get the syntax correct. Here's what I have so far:
SELECT SQLUser_MEMBER_ACCT.acn, SQLUser_MEMBER_ACCT.acn_Status, SQLUser_MEMBER_ACCT.mbr_nam INTO f_members
FROM SQLUser_MEMBER_ACCT
CREATE TABLE f_members2
(
acn int NOT NULL PRIMARY KEY,
acn_Status varchar(255) NOT NULL,
mbr_nam varchar(255) );


I'm not totally sure what you are trying to do, but I get the jist of it.

Try this:

SELECT 
acn
,acn_Status
,mbr_nam 
FROM SQLUser_MEMBER_ACCT
;

CREATE TABLE  f_members2 AS  
( acn Integer NOT NULL, acn_Status Varchar(255) NOT NULL,
mbr_nam Varchar(255)
)
PRIMARY INDEX (acn)
;

I presumed you wanted to: select three fields from SQLUser_MEMBER_ACCT table, then CREATE an empty table with columns defined, and setting a primary key on the table)

If this isn't what you need please explain clearly.

Regards,
dev00790

Edited by dev00790, 09 November 2011 - 02:25 PM.

Regards, dev00790

---------------------------------------

Marge: "Homer, the plant called. They said if you don't show up tomorrow don't bother showing up on Monday." Homer: "Woo-hoo! Four-day weekend!"I do not reply to Private Messages (PMs) asking for assistance - please use the forums instead. If I have been helping you, and I have not replied to your latest post in 48 hours please send me a PM. My Blog


#7 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 02:45 PM

Hi there:

You seem to be on the right track there.
Not sure where I'm being unclear? A little history:

I was able to write my query to SELECT the "SQLUser_MEMBER_ACCT" table and its three columns in question. THIS part worked perfectly.
The end result was the CREATE TABLE: "f_members".

Up to this point, everything works great.

However, the newly created table "f_members" needs a primary key to be searchable (it's going to be part of an Infopath repository).
Herein lies the problem; defining a primary key on the "f_members" table only works once; as the query updates the table (daily), it will overwrite the primary key specification, rendering the table useless. The query should RETAIN the primary key definition on the "acn" column - something that out-of-the-box MS Access cannot do. You can only define primary keys from the table design mode.

dev00790, your interpretation was correct but the syntax still fails MS Access' compiler. It expects a full stop after the first semicolon. MS Access does not support nested SQL statements. Is is possible at all to have these commands in ONE argument?

I sincerely appreciate your time and look forward to your response.




I'm not totally sure what you are trying to do, but I get the jist of it.

Try this:

SELECT 
acn
,acn_Status
,mbr_nam 
FROM SQLUser_MEMBER_ACCT
;

CREATE TABLE  f_members2 AS  
( acn Integer NOT NULL, acn_Status Varchar(255) NOT NULL,
mbr_nam Varchar(255)
)
PRIMARY INDEX (acn)
;

I presumed you wanted to: select three fields from SQLUser_MEMBER_ACCT table, then CREATE an empty table with columns defined, and setting a primary key on the table)

If this isn't what you need please explain clearly.

Regards,
dev00790
[/quote]

Edited by groovicus, 09 November 2011 - 03:11 PM.
Really no need to quote everything.

fueled by coffee™

#8 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:05:55 AM

Posted 09 November 2011 - 03:17 PM

What about:
Create table foo (PRIMARY KEY (chan1l)) SELECT chan1, chan2 FROM bar;

I know it works because I just did the exact same thing a couple of days ago.

#9 dev00790

dev00790

    Bleeping Chocoholic


  • Members
  • 5,037 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:11:55 AM

Posted 09 November 2011 - 03:30 PM

Ok I 've a plan now.

First save these as separate queries:

1)

SELECT 
acn
,acn_Status
,mbr_nam 
FROM SQLUser_MEMBER_ACCT
;

Save it as "Qry_sel_3cols"

2)

CREATE TABLE  f_members2
( acn Integer NOT NULL CONSTRAINT  acn PRIMARY KEY
, acn_Status Varchar(255) NOT NULL
,mbr_nam Varchar(255) 
)
;

Save it as "Qry_create_f_members2"

3) Now I'm aware of a couple of ways (off the top of my head) that you could get both queries to run - one after another. One is via a Macro. Another is via VBA for Access.

- Which of these are you familiar more with(if any)? My plan is too try and do the one which may be easier for you to understand - hence me asking about your current knowledge.

Regards, dev00790

---------------------------------------

Marge: "Homer, the plant called. They said if you don't show up tomorrow don't bother showing up on Monday." Homer: "Woo-hoo! Four-day weekend!"I do not reply to Private Messages (PMs) asking for assistance - please use the forums instead. If I have been helping you, and I have not replied to your latest post in 48 hours please send me a PM. My Blog


#10 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 03:35 PM

groovicus:

I copied your query structure and added my variables into the query. However, I am still unable to get it to compile correctly.
Here's my version:

CREATE TABLE f_members2 (PRIMARY KEY (acn)) SELECT acn, acn_Status,mbr_nam FROM SQLUser_MEMBER_ACCT;

Again, it takes issue with the syntax. MS Access seems to think a reserved word is being used as a variable. I believe it is due to the source table being named "SQLUser_MEMBER_ACCT". According to SQL for Dummies, "MEMBER" is a reserved word.

I will follow up with my test results but I believe this is the answer.


What about:
Create table foo (PRIMARY KEY (chan1l)) SELECT chan1, chan2 FROM bar;

I know it works because I just did the exact same thing a couple of days ago.


fueled by coffee™

#11 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:05:55 AM

Posted 09 November 2011 - 03:42 PM

'Member' is a reserved word. 'SQLUser_MEMBER_ACCT' is not. What is the exact error that you are getting?

#12 dev00790

dev00790

    Bleeping Chocoholic


  • Members
  • 5,037 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:11:55 AM

Posted 09 November 2011 - 03:44 PM

"SQLUser_MEMBER_ACCT". According to SQL for Dummies, "MEMBER" is a reserved word.


Yes I believe that MEMBER is a reserved word, however when used as part of a string like "SQLUser_MEMBER_ACCT", using it will not be a problem.

Have a look at my previous post & let me know your thoughts.

Regards, dev00790

---------------------------------------

Marge: "Homer, the plant called. They said if you don't show up tomorrow don't bother showing up on Monday." Homer: "Woo-hoo! Four-day weekend!"I do not reply to Private Messages (PMs) asking for assistance - please use the forums instead. If I have been helping you, and I have not replied to your latest post in 48 hours please send me a PM. My Blog


#13 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 04:04 PM

I think I would be more comfortable using macros, but I'm not above going the VB route. Which would be easier?

Ok I 've a plan now.

First save these as separate queries:

1)

SELECT 
acn
,acn_Status
,mbr_nam 
FROM SQLUser_MEMBER_ACCT
;

Save it as "Qry_sel_3cols"

2)

CREATE TABLE  f_members2
( acn Integer NOT NULL CONSTRAINT  acn PRIMARY KEY
, acn_Status Varchar(255) NOT NULL
,mbr_nam Varchar(255) 
)
;

Save it as "Qry_create_f_members2"

3) Now I'm aware of a couple of ways (off the top of my head) that you could get both queries to run - one after another. One is via a Macro. Another is via VBA for Access.

- Which of these are you familiar more with(if any)? My plan is too try and do the one which may be easier for you to understand - hence me asking about your current knowledge.


fueled by coffee™

#14 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:05:55 AM

Posted 09 November 2011 - 04:07 PM

If the single query does not work, then this will:
CREATE TABLE Foo SELECT chan1, chan2 FROM Bar;
ALTER TABLE Foo SET PRIMARY KEY (chan1);

Or you can do it the hard way, completely ignoring the fact that SQL has built in capabilities for doing this. <_<

#15 mybuddyeric

mybuddyeric
  • Topic Starter

  • Members
  • 51 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Virginia
  • Local time:06:55 AM

Posted 09 November 2011 - 04:09 PM

The error is encountered during an attempt to save the query.
The error reads: "Syntax error in CREATE TABLE statement".

'Member' is a reserved word. 'SQLUser_MEMBER_ACCT' is not. What is the exact error that you are getting?


fueled by coffee™




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users