Import Data From Other Database and Eliminate Duplicates
-
I need to import data from one Access database to another with virtually identitical structurers (the target database may have more columns, but it has every column that the database to be imported has). I want to do this for specific tables using SQL, but my database is protected by a database password. How do I construct the SQL statement to include the password? Thanks.
-
I need to import data from one Access database to another with virtually identitical structurers (the target database may have more columns, but it has every column that the database to be imported has). I want to do this for specific tables using SQL, but my database is protected by a database password. How do I construct the SQL statement to include the password? Thanks.
polishprogrammer wrote:
How do I construct the SQL statement to include the password
The SQL statement doesn't include the password, only the connection does. The solution varies depending which tools you're about to use, but basically you take one connection to the access db, read data from there and put it to sql server using another connection. You could do this with c#, SSIS/DTS (depending on the db version), linked server etc.
The need to optimize rises from a bad design.My articles[^]
-
polishprogrammer wrote:
How do I construct the SQL statement to include the password
The SQL statement doesn't include the password, only the connection does. The solution varies depending which tools you're about to use, but basically you take one connection to the access db, read data from there and put it to sql server using another connection. You could do this with c#, SSIS/DTS (depending on the db version), linked server etc.
The need to optimize rises from a bad design.My articles[^]
Thanks, but both the databases are MS Access databases. I need to import data from one Access database into another Access database. I will be using C# to do this operation. How would I include the connection in any SQL operation I might perform? I've tried to Merge 2 data tables. It works (usually), but takes a VERY long time. That's why I was hoping to find something quicker, such as a SQL statement perform the work. If my databases were not secure, I could do this very easily, but the database password complicates things. Thanks.
-
Thanks, but both the databases are MS Access databases. I need to import data from one Access database into another Access database. I will be using C# to do this operation. How would I include the connection in any SQL operation I might perform? I've tried to Merge 2 data tables. It works (usually), but takes a VERY long time. That's why I was hoping to find something quicker, such as a SQL statement perform the work. If my databases were not secure, I could do this very easily, but the database password complicates things. Thanks.
I guess I would open a connection to the source db and then loop through the data and put the data to the target db using another connection. So I wouldn't use data tables in the middle. Also for removing duplicates I would order the source data. It's been awhile since I last used access so there may be another ways, but I think the logic I described would get the job done.
The need to optimize rises from a bad design.My articles[^]
-
I guess I would open a connection to the source db and then loop through the data and put the data to the target db using another connection. So I wouldn't use data tables in the middle. Also for removing duplicates I would order the source data. It's been awhile since I last used access so there may be another ways, but I think the logic I described would get the job done.
The need to optimize rises from a bad design.My articles[^]
Mika, I found this statement worked to import the data, but it does not take care of the issue of duplicates. I need to customize this statement a bit to ensure that I'm only selecting those records from the C:\Program Files\Northwind.mdb database, say, that are not already in the target database. Sort of, perhaps, a reverse INNER JOIN. If you have any suggestions on that, I would appreciate it. Thanks. INSERT INTO Employees SELECT Employees.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees;
-
Mika, I found this statement worked to import the data, but it does not take care of the issue of duplicates. I need to customize this statement a bit to ensure that I'm only selecting those records from the C:\Program Files\Northwind.mdb database, say, that are not already in the target database. Sort of, perhaps, a reverse INNER JOIN. If you have any suggestions on that, I would appreciate it. Thanks. INSERT INTO Employees SELECT Employees.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees;
Aah, they've added external sources to Access. Starts to look like SQL Server. Anyway, I don't know how you identify a duplicate, but let's say you have a column named
IDand based on that you make the decision if it exists or not. In that case you could have for example:INSERT INTO Employees
SELECT source.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees source
WHERE NOT EXISTS (SELECT 1
FROM Employees target
WHERE target.ID = source.ID)or even better if
EXCEPTis supported by Access, you could have:INSERT INTO Employees
SELECT *
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees
EXCEPT
SELECT *
FROM EmployeesThe columns must be in the same order in both tables in all of the statements in order for them to work.
The need to optimize rises from a bad design.My articles[^]
-
Aah, they've added external sources to Access. Starts to look like SQL Server. Anyway, I don't know how you identify a duplicate, but let's say you have a column named
IDand based on that you make the decision if it exists or not. In that case you could have for example:INSERT INTO Employees
SELECT source.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees source
WHERE NOT EXISTS (SELECT 1
FROM Employees target
WHERE target.ID = source.ID)or even better if
EXCEPTis supported by Access, you could have:INSERT INTO Employees
SELECT *
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees
EXCEPT
SELECT *
FROM EmployeesThe columns must be in the same order in both tables in all of the statements in order for them to work.
The need to optimize rises from a bad design.My articles[^]
After some search (and trial and error), I found this statement to work: INSERT INTO Employees SELECT e1.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].as e1 LEFT JOIN Employees as e2 ON e1.Field1=e2.Field1 WHERE e2.Field1 IS NULL The IS NULL comparison selects only those records from the left side of the join that did not already have a match in the right (target) table. It seems to work well. When I tried to import records through code (DataTable.Merge or some iteration technique), the import process would take many minutes for moderately sized tables (30000 to 70000 records), but using just the SQL it took the application about 6 seconds do the same operation. The only major change I had to make when implementing this in code was to insert the appropriate variable for the database path and implement a function to get the password, as appropriate, such as Marshal.PtrToStringAuto(logPointer). Thanks again for your suggestions and I hope this follow up I provided is useful.
-
After some search (and trial and error), I found this statement to work: INSERT INTO Employees SELECT e1.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].as e1 LEFT JOIN Employees as e2 ON e1.Field1=e2.Field1 WHERE e2.Field1 IS NULL The IS NULL comparison selects only those records from the left side of the join that did not already have a match in the right (target) table. It seems to work well. When I tried to import records through code (DataTable.Merge or some iteration technique), the import process would take many minutes for moderately sized tables (30000 to 70000 records), but using just the SQL it took the application about 6 seconds do the same operation. The only major change I had to make when implementing this in code was to insert the appropriate variable for the database path and implement a function to get the password, as appropriate, such as Marshal.PtrToStringAuto(logPointer). Thanks again for your suggestions and I hope this follow up I provided is useful.