SQL Server Performance Forum – Threads Archive

table update question

Great Forum! First of all, I am BRAND NEW to SQL server (2005 standard), but I am an experienced Access user. I have successfully linked a table from an MDB file to my SQL server, and have created a make table query that allows me to migrate table data into the server. I am trying to create an update query that I can just run to suck the data out of the access source every day and parse it into the new table. In access this is pretty simple. Access will not migrate existing PKs from the source table into the target table, so only new records are added to the target (PKs from source that do not exist in target). SQL server does not allow this. I understand why this is, however, it seems to me that I can write an update query from the linked table to the server table and by using a where clause, insure that the PKs from the source and target tables are not equal so that SQL server does not kill the statement. Here is my SQL statement: INSERT INTO [all data]
SELECT *
FROM [ALL MAINTENANCE]…[all data] AS sql_all_data
where sql_all_data.identifer<>[all data].identifer and here is the error that I get: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "all data.identifer" could not be bound. “Identifier” is my PK.

---

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=374441&SiteID=1 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks, very much for the reply. I am afraid the MSDN posts and linked articles did not help me out. I tried changing the database type to 80 and still no go, same error. The MSDN posts talks about Aliases–As far as alias table names, my linked source is the alias but the error says the problem is with the target. I tried changing the target table to something with no white spaces to get rid of the [], but still no go. I’ve found a work around: DELETE FROM [all data] INSERT INTO [all data]
SELECT *
FROM [ALL MAINTENANCE]…[all data] AS sql_all_data …But I don’t like it. It too ham handed to delete 300K rows when all you want to do is append 1 or 2K to the dataset. I’d like to solve it with a regular append query with the where clause excluding PKs already on the target, if possible.

---

Insert into [all data]
Select columns from sourceTable S
where not exists(select * from [all data] where pkcolumn=S.pkcolumn)
Madhivanan Failing to plan is Planning to fail

---

Madhivanan- Thanks!

---

Works like a champ! Thanks Again! INSERT INTO [all data]
SELECT *
FROM [ALL MAINTENANCE]…[all data] AS sql_all_data
where not exists(select * from [all data] where identifer=sql_all_data.identifer)

---

You are welcome Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Madhivanan Failing to plan is Planning to fail

---

Thanks for the links. The implementation of SQL in Access is a bit different than in SQL Server 2005. For instance, my first SQL statement with the "<>" works fine in Access
but not in SQL server. I would have never though I needed to add an extra select statement. I understood what you did right after I read you mail. The outer select actually excludes the records and the inner select sets the critera for selection. Thanks again for the links and your help.

---

The syntax of Access is somewhat different from SQL Server. You need to learn the difference by referring to those links Madhivanan Failing to plan is Planning to fail

---

I would say there is lot of difference between Access & SQL Server 2005, recently MS begins to put pure database engine on Access but it is an other world as you compare both of them. Also go thru Microsoft sites, as they put up the subtle differences between these 2 products in a form of white paper and a demo. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I’ve heard that Office 2k3 is the last implementation of JET in MS ACCESS. I wonder if they are going to base the new engine on SQL server? From what I’ve read, ADPs are on their way out. Access really can’t front end SQL server 2k5. It can read 08 type databases but cannot change objects in them. This is the primary reason I want to learn 2k5, because it seems that MS is doing away with ADPs as a method to access SQL server back end sources. Kind of seems a pity, because there are probably lots of programmers who know ACCESS very well. Check our what Mary Chipman has to say: http://www.databaseadvisors.com/gazette/sqlexpress.htm "You will not be able to use any of the designers with SQLS 2005 databases, whether it’s SQL Express or the Developer edition. IOW, you won’t be able to create databases, tables, views or any other database objects from an ADP. The only support that is envisioned is that you will be able to connect an Access front-end to a SQLS 2005 back end if it is running in SQLS 2000 compatibility mode, so your forms, reports and other local Access objects should still run. There is no service pack or quick fix being planned as far as I know because of the amount of work it would entail. If you stop to think about it, it’s pretty hard to see how accomodating new Yukon features like CLR assemblies and complex data types in the ADP designers could be achieved without a complete rewrite. That said, with Access 2003, I was able to connect up to an instance of SQL 2005 (not in 2000 compatibility mode) and work with data with SQL2000 compatible data types. I was also able to stick XML into an XML-typed (but not strongly-typed) column and have it work as expected. The bottom line here seems to be that ADPs aren’t worth investing new work into today if you plan to go to SQL Server 2005 with them. However, my limited testing of Access 2003 as the Frontend and SQL Server 2005 as backend using linked tables seems to be okay. Time will tell, of course. " I really love this forum. If you read any interesting articles about the next version of ACCESS I’d love to read them!

---

Glad to know you found this forum as useful for your issues, I mean to say MS may put SQL (kind of) engine in the next release of Access 2007 (probably). Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>