I'm having:

«Insert Error: Column name or number of supplied values does not match
  table definition.»

with the following query:

INSERT into table1 /* table1 is in SqlServer database */
    SELECT *
    FROM table1 dest
    right OUTER JOIN db_access...table1 src  /* dblink to MS access */
    ON dest.col1 = src.col1
    AND dest.col2 = src.col2
    WHERE dest.col2 IS NULL

do i have to give the 50 column name of table1 to get it work?

  insert into table1(col1, col2, col3, ..., col50)
    select col1, col2, col3, ..., col50
    ...

table1 from both database are identical

Thank you

INSERT into table1 /* table1 is in SqlServer database */
    SELECT src.*
    FROM table1 dest
    right OUTER JOIN db_access...table1 src  /* dblink to MS access */
    ON dest.col1 = src.col1
    AND dest.col2 = src.col2
    WHERE dest.col2 IS NULL

This should work

http://sqlservercode.blogspot.com/




Show quoteHide quote

then use table1.* in the select statement.

INSERT into table1 /* table1 is in SqlServer database */
    SELECT table1.*
    FROM table1 dest
    right OUTER JOIN db_access...table1 src  /* dblink to MS access */
    ON dest.col1 = src.col1
    AND dest.col2 = src.col2
    WHERE dest.col2 IS NULL


AMB

Show quoteHide quote

Correction,

then use src.* in the select statement.

INSERT into table1 /* table1 is in SqlServer database */
    SELECT src.*
    FROM table1 dest
    right OUTER JOIN db_access...table1 src  /* dblink to MS access */
    ON dest.col1 = src.col1
    AND dest.col2 = src.col2
    WHERE dest.col2 IS NULL


AMB

Show quoteHide quote

You don't have to but it's the best way. Using SELECT * or INSERT
without a column list is careless, inefficient and a headache to
support. To click and drag the list of object names in Query Analyzer
takes less than 2 seconds so it seems silly not to.

I correct with src.* and now i get this error message 260:

Disallowed implicit conversion from data type ...

it see a data type in date time instead of real. I think it is coming
from the "Microsoft Jet 4.0 OLE DB Provider" use for my link
"db_access"

when is use:
   insert into table1 select * from openquery(db_access, 'select * from
table1)

i got the constraint violation error as expected.

any idea?

If this is a one-time maintenance insert, then no, it's not necessary to
supply the 50 column names.  If it is part of a stored procedure, DTS
package or embedded in application code, then yes, you should definitely
enumerate the 50 column names.  There are many, many reasons not to use
SELECT *, ranging from performance to maintenance.  It's considered a very
BAD practice usually committed by the lazy or incompetent.  If you must use
it (I can't think of a good reason, and I've been in this business for
decades), you should clearly document within the code the reasons behind
committing such an egregious breech of accepted norms.

I'm having:

«Insert Error: Column name or number of supplied values does not match
  table definition.»

with the following query:

INSERT into table1 /* table1 is in SqlServer database */
    SELECT *
    FROM table1 dest
    right OUTER JOIN db_access...table1 src  /* dblink to MS access */
    ON dest.col1 = src.col1
    AND dest.col2 = src.col2
    WHERE dest.col2 IS NULL

do i have to give the 50 column name of table1 to get it work?

  insert into table1(col1, col2, col3, ..., col50)
    select col1, col2, col3, ..., col50
    ...

table1 from both database are identical

Thank you

I use the Brian and David suggestion and it work perfectly. It correct
the previous POST.

Thank you both!

Question about referencing UDFs
Torn over a normalizing decision
Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations