Cannot insert into table because the table already

问题:

I have a user table. I want to insert data into my user table.

I have a statement:

SELECT columna, columnb, 
INTO my_table 
FROM my_other_table
WHERE (... conditions ...)

I get the following error:

SQL Server Error on (myserver) Error:2714 at Line:1 Message:There is already an object named 'my_table' in the database.

Yes, thanks Sybase. I know this. I know the table exists. I want to insert data into it.

Why is Sybase not playing nicely? :(

(Sybase isn't my forte, Oracle is. This may just be an understanding issue, or lack there of. This would never happen in Oracle...)

回答1:

SELECT ... INTO is for creating new tables.

Use INSERT ... SELECT for existing tables. eg:

INSERT INTO my_table 
SELECT columna, columnb, 
FROM my_other_table
WHERE (... conditions ...)

回答2:

Have you tried it this way around?

Insert INTO my_table
SELECT columna, columnb, 
FROM my_other_table
WHERE (... conditions ...)

It appears that it is trying to implicitly create a new table for you called my_table.

回答3:

Not sure of SYBASE but in DB2 this works for me

 INSERT INTO my_table
 (
      columna,
      columnb
 )
 SELECT
      columna,
      columnb
 FROM
      my_other_table
 WHERE
      (... conditions...)

I think its safer to specify the columns in the insert statement as well rather than assume they'll be in the same order as the select.

回答4:

Use 'existing' keyword after 'into' to insert in existing table.

SELECT [COLUMN LIST] INTO EXISTING [TABLE NAME]