Selecting Into Temp Table From Table With GUIDs

[Former Member]

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;

[MarkCulp]

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

[Former Member]

10.0.1.4036....

[Former Member]

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

[MarkCulp]

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

[Former Member]

Added..........

[Breck_Carter]

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;

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

I need the result table to be persistent.

[VolkerBarth · Accepted solution]

@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" ?

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[VolkerBarth · Accepted solution]

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

[Breck_Carter · Accepted solution]

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

[Former Member · Accepted solution]

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

[VolkerBarth · Accepted solution]

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

[thomas_duemesnil]

Can't you use

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

[Former Member]

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.