Invalid object name!

Hi,
I'm trying to create a new table by merging two files together. They both
have exactly the same table structure. I.e. they are both got 1 field called
ref varchar(255).
The code I'm using is:
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
The error message I'm getting is:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'U_T_XmasOnly'.
Can you please tell me what I'm doing wrong?
Thanks in advance
RobRobert
I think you missed INTO within the INSERT statement
It shoul be
INSERT INTO U_T_XmasOnly(REF)
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> Hi,
> I'm trying to create a new table by merging two files together. They both
> have exactly the same table structure. I.e. they are both got 1 field
called
> ref varchar(255).
> The code I'm using is:
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> The error message I'm getting is:
>
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'U_T_XmasOnly'.
> Can you please tell me what I'm doing wrong?
> Thanks in advance
> Rob|||The INTO is optional, so the INSERT statement is fine, as long as the table
already exists. Your object 'U_T_XmasOnly' musn't exist.
Looking at your statement "I'm trying to create a table" I think what you
want to do is this:
SELECT ref
INTO U_T_XmasOnly
FROM
(
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
) x
But what you should really do is create your table first, then use the
INSERT syntax above:
eg
CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
INSERT U_T_XmasOnly(REF)
SELECT DISTINCT ref
FROM U_T_AttXmasOnly
UNION
SELECT DISTINCT ref
FROM U_T_BlkXmasOnly
Basically the syntax for adding records to an _existing_ table is the
INSERT, creating a table on they fly and adding records at the same time is
the SELECT INTO, and really you should create the table first, then add the
records, using the CREATE TABLE and INSERT.
Let me know how get on.
Damien
"Uri Dimant" wrote:

> Robert
> I think you missed INTO within the INSERT statement
> It shoul be
> INSERT INTO U_T_XmasOnly(REF)
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:8C2270C4-0CC1-45BB-95D4-C4539A00C1BB@.microsoft.com...
> called
>
>|||Hehehe, right , I have never thought about it.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:45A4A9EE-A080-45AC-85E7-E3DC24EB2BD6@.microsoft.com...
> The INTO is optional, so the INSERT statement is fine, as long as the
table
> already exists. Your object 'U_T_XmasOnly' musn't exist.
> Looking at your statement "I'm trying to create a table" I think what you
> want to do is this:
> SELECT ref
> INTO U_T_XmasOnly
> FROM
> (
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> ) x
> But what you should really do is create your table first, then use the
> INSERT syntax above:
> eg
> CREATE TABLE U_T_XmasOnly ( ref INT) -- other columns etc
> INSERT U_T_XmasOnly(REF)
> SELECT DISTINCT ref
> FROM U_T_AttXmasOnly
> UNION
> SELECT DISTINCT ref
> FROM U_T_BlkXmasOnly
> Basically the syntax for adding records to an _existing_ table is the
> INSERT, creating a table on they fly and adding records at the same time
is
> the SELECT INTO, and really you should create the table first, then add
the
> records, using the CREATE TABLE and INSERT.
> Let me know how get on.
>
> Damien
> "Uri Dimant" wrote:
>
both