"IN" keyword sql file path

Question

While moving an app from access to sql server I have come across a keyword that is causing errors. The keyword within the query is "IN". INSERT INTO Table IN 'file path of sql server database'. Does sql server allow you to insert data using the file path within the query? I have been researching this for a few days and have come up with nothing.

Thanks

Answer 1

The IN keyword in Microsoft Access in this syntax allows you to specify another database file where the table is. This is equivalent to being in another database in Sql Server.

It looks like you are taking multiple Access database files and importing them into multiple Sql Server databases. Let's say you have an Access database called One.MDB and it gets some data from a table in Access database Two.MDB, and you have imported into Sql Server into databases One and Two. So you need to get the data from the other database. Sql Server uses a syntax of Database.Schema.Table (the schema by default is the database owner schema, or dbo).

So this in Microsoft Access:

INSERT INTO MyTable IN 'Two.MDB' ...

translates to this in Sql Server:

INSERT INTO Two.dbo.MyTable ...

That's assuming you are still using separate databases. If you've put it all in the same database, you just need to reference the table directly:

INSERT INTO MyTable ...