Query working on SQL Server but not on Access

  • Thread starter Thread starter Sameer Motwani
  • Start date Start date
S

Sameer Motwani

Hi,
The following query

INSERT INTO Table1
SELECT *, const1,const2,const3
FROM Table2

inserts multiple rows from Table2 to Table1, where the structure of Table1
contains all columns of Table2 plus 3 additional fields.

The query shown above works correctly on SQL Server but fails on Access.
Does anyone know why does this happen and is it possible to have a single
SQL query that works correctly on both SQL Server and Access.

Any help would be highly appreciated

Thanks!

Sameer
 
I generally enter all field names. Also, it is difficult to troubleshoot
when we don't see the actual SQL or know what you mean by "fails".
 
Hi Sameer,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

Microsoft Access are using Jet SQL while SQL Server using T-SQL, there are
some differences between them, including some keywords and syntax. For
multiple-record append query, the syntax is:

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

You should using the fileds' names while SQL Server could not.
In ACCESS:
INSERT INTO table2(a,b,c,d)
SELECT '1','1', a,b
FROM table3;
While in SQL Server, it could be
INSERT INTO table2
SELECT '1','1', a,b
FROM table3;

Is this the answer you want? If not or if you still have questions, please
feel free to post new message here and I am ready to help!

Baisong Wei
Microsoft Online Support
 
Hi Baisong ,
Thanks for replying.

The problem is that I don't want to type the Field names of the table since
both the tables have more than 80 fields but I want to keep the same query
because I am executing the query from my VC++ code using ODBC to access
the Database and I don't want to change the code when I change database
changes.

I observed that the following query
INSERT INTO table2
SELECT *
FROM table1

does execute on both Access and SQL when structure of table2 and table1 are
same.

But in my database table2 has all fields of table1 plus some additional
fields (which are not null)
and therefore I require a query that will insert all data from table1 plus
additional data into table2

Thank You,
Sameer
 
Hi Sameer,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

As I mentioned in my last post, there are some syntax or keywork difference
between the Jet SQL and Transaction SQL. It is all right to run sql as
follows if the table2 has less columns than table2 and the default value or
Null is inserted for missing columns:
INSERT INTO table1
SELECT *
FROM table2

In your case, if colums are the same, this sql statement could work in both
Access and SQL Server. However, as I mentioned in my last post, the valid
sql statement should be as follows:
In ACCESS:
INSERT INTO table1(a,b,c,d)
SELECT '1','1', a,b
FROM table2;

Field should be set in Access while in SQL Server, it could be
INSERT INTO table1
SELECT '1','1', a,b
FROM table2;

For your case, I think you can use subquery as follows (Tested in both
Access and SQL Server, . In Access, table1have four columns: a, b, c, d are
all 'number; table2 has 2 columns: a, b are all numbers; In SQL Server, all
column are int):

insert into table1
select * from (select *, 1 as c, 2 as d from table2 ) as x

Note: In the subquery 'select *, 1 as c, 2 as d from table2 ', 'c' and 'd'
should be the column name in table1.

Hope this helps. If you still have questions about it, please feel free to
post new message here and I am ready to help. Looking forward to you reply!

Baisong Wei
Microsoft Online Support
 
Hi Baisong,

Would you mind checking the
following "rewrite" for accuracy?
I wanted to save it to be able to
go back to at some later time and
the following is how I understand
what was said (rightly or wrongly):

There are some syntax or keyword differences
between the Jet SQL and Transaction SQL.

If table2 has less columns than table1,
but the name and type are the same for the
columns they share in common,
and a default value or Null is inserted
for missing columns in table1:

INSERT INTO table1
SELECT *
FROM table2

the above sql statement should work if both tables
are in Access or both tables are in SQL Server.

Otherwise, if you wish to provide the default values,
the sql statement should be as follows:

In ACCESS:

INSERT INTO table1(a,b,c,d)
SELECT '1','1', a,b
FROM table2;

In SQL Server:

INSERT INTO table1
SELECT '1','1', a,b
FROM table2;

For the case where "AppendTo" table1 is in Access
and the "GetValues" table2 is in SQL Server (or Access),
table2 has fewer columns than table1,
the columns they share in common have same name and type,
and you want to provide values for missing columns in table2,
I think you can use subquery as follows
(actually tested in both Access and SQL Server)

In Access,
table1 has four columns: a, b, c, d (all type number)
table2 has 2 columns: a, b (both type number)
In SQL Server,
table2 has 2 columns: a, b ( all columns are type int):

insert into table1
select * from (select *, 1 as c, 2 as d from table2 ) as x

Note: In the subquery 'select *, 1 as c, 2 as d from table2 ',
'c' and 'd' should be the column name in table1.
 
Hi Baisong,
The solution worked for me.

I really appreciate your help.

Thank You,

Sameer
 
Hi Gary,

Yes, that is exactaly what I mean. I think it would be better if we take
some datatype conversion into consideration.

In access, I create a table3
table3 has 2 columns: a number, b text.

All the following statements are OK
insert into table3 values('1','1')
-- insert a text to a number column is fine, but if a '3a' will failed for
type mis-match
insert into table3 values( 2 ,'1')
-- strictly the same as the column type
insert into table3 values( 3 ,1)
-- number to text column is fine

In SQL Server, this is simular. If you have any additional value on it, we
could discuss it here. Thanks.

Baisong Wei
Microsoft Online Support
 
Thank you Baisong for your help.

We've gone into crunch time here at
work and I apologise for not getting
back sooner.

gary
 
Back
Top