The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

I need to be able to select into a temporary table (which is really a copy of the base table, not really 'temporary'). The problem I'm running into is that the base table uses GUIDs for the Primary Key, and upon attempting to run a:

SELECT * INTO HOLD_TABLE FROM ORIGINAL_TABLE WHERE CRITERIA IS NULL;

I keep getting a Syntax Error Near '(' on Line 1)

The only thing I can come up with is because the table has a GUID for the Primary Key.

Has anybody ran into this before, or have any tips on circumventing the issue?

Thanks, Calvin

EDIT:

Table Structure:

CREATE TABLE "DBA"."Consumer_Plan_Section_Services" (
    "CP_Assessment_Service_ID" uniqueidentifier NOT NULL,
    "Consumer_Plan_ID" uniqueidentifier NOT NULL,
    "Template_Section_ID" uniqueidentifier NOT NULL,
    "Service_Needed" long varchar NULL,
    "Service_ID" double NULL,
    "Service_Type_ID" double NULL,
    "Frequency" integer NULL,
    "Duration" char(1) NULL,
    "Vendor_ID" double NULL,
    "Type_of_Provider" varchar(10) NULL,
    "Type_ID" double NULL,
    "User_Id" varchar(30) NULL,
    "Last_Update" timestamp NULL,
    "Remote_Location" integer NULL,
    "Funding_Source_ID" double NULL,
    "Effective_Start_Date" date NULL,
    "Effective_End_Date" date NULL,
    "Staff_Training_Required" char(1) NULL DEFAULT 'N',
    "Service_Levels" varchar(256) NULL,
    "Order_Number" integer NOT NULL DEFAULT 0,
    "Freq_Duration_Desc" varchar(100) NULL,
    PRIMARY KEY ( "CP_Assessment_Service_ID" ASC )
);

Select Statement:

SELECT * INTO Invalid_CP_Section_Services FROM Consumer_Plan_Section_Services WHERE Vendor_ID IS NULL;

asked 21 Jun '10, 17:26

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 21 Jun '10, 20:41

This sounds like a bug. What exact version and build number are you using?

(21 Jun '10, 19:35) Mark Culp

10.0.1.4036....

(21 Jun '10, 20:17) Calvin Allen

I can also give more exact table definitions if anybody wants to see them.

(21 Jun '10, 20:22) Calvin Allen

Yes, please show us your table definitions. I've tried to repro the issue and was not able to.

(21 Jun '10, 20:30) Mark Culp

Added..........

(21 Jun '10, 20:42) Calvin Allen

Works in V11 with or without the # prefix, but it looks like the # prefix is necessary in V10 if you have any uniqueidentifier columns: SELECT * INTO #Invalid_CP_Section_Services FROM Consumer_Plan_Section_Services WHERE Vendor_ID IS NULL;

(22 Jun '10, 08:19) Breck Carter
More comments hidden
showing 5 of 6 show all flat view

The problem has been found and will be fixed in 10.0.1.4088. Later versions (11.*, etc.) already contained the fix. The problem did not affect SELECT ... INTO when creating temporary tables, and happened when the source query's SELECT list contained UNIQUEIDENTIFIER columns.

permanent link

answered 22 Jun '10, 13:45

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

edited 25 Jun '10, 13:09

I need the result table to be persistent.

(22 Jun '10, 14:30) Calvin Allen

@Calvin: Would it be sufficient to create the table Invalid_CP_Section_Services explicitely (i.e. by CREATE TABLE) and then use "INSERT Invalid_CP_Section_Services SELECT * FROM Invalid_CP_Section_Services" ?

(22 Jun '10, 18:51) Volker Barth

@Volker: That is an option I considered, and will go with if there is no alternative.

(22 Jun '10, 20:31) Calvin Allen

For the record, SELECT ... INTO can be used to create permanent tables starting with version 11. See the conditions in the documentation (mainly that the SELECT list must contain more than one item, or if * is used then the table name must be given as owner.name). Doesn't help you with 10.0.1 though.

(23 Jun '10, 13:42) Bruce Hay

@Bruce - The statements work as expected in 10.0.1 unless the table has a GUID primary key.

(23 Jun '10, 22:24) Calvin Allen
3

Okay, the problem has been found and will be fixed in 10.0.1.4088. Later versions (11.*, etc.) already contained the fix. The problem did not affect SELECT ... INTO when creating temporary tables. The problem happened when the source query contained UNIQUEIDENTIFIER columns, not just as the primary key. My earlier statement about SELECT ... INTO creating permanent tables starting in 11 was wrong; it's supported and documented in 10 as well.

(24 Jun '10, 13:41) Bruce Hay
Comment Text Removed

Awesome! Thank you! If you revise your answer for that, I'll mark it as accepted so you can get some brownie points!

(24 Jun '10, 14:47) Calvin Allen

@Calvin: A bounty for a bugfix - that's a fair deal, methinks:)

(24 Jun '10, 15:57) Volker Barth

@Calvin: Why don't you modify Bruce's answer for him, and give him the bounty?

(25 Jun '10, 10:37) Breck Carter

@Breck - I evidently don't have enough rep to edit answers.

(25 Jun '10, 11:07) Calvin Allen

@Calvin: And now even less:) - @Breck: FYI: I can't edit someone else's answers, either.

(25 Jun '10, 14:27) Volker Barth
More comments hidden
showing 5 of 11 show all flat view

Can't you use

INSERT INTO Invalid_CP_Section_Services  
SELECT *  
FROM Consumer_Plan_Section_Services  
WHERE Vendor_ID IS NULL;
permanent link

answered 23 Jun '10, 10:23

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
accept rate: 17%

The table does not exist prior to running these statements. The idea is to have it created on the fly by our upgrade script (which is ran by our IT staff on our clients servers), and then persist if someone needs to review the data.

(23 Jun '10, 17:47) Calvin Allen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×14
×4
×1

question asked: 21 Jun '10, 17:26

question was seen: 1,763 times

last updated: 25 Jun '10, 13:09