Insert date/time records from access into a sql database.
I have an application that pulls dates and numbers from an access database. I need that database to be in SQL, aso I created a new table - In access the values where formatted as date/time. When I created it the new table in SQL, I formated it as small date time.
I need to insert all of the records from access into the database. When I pull up the ScriptTable as-->INSERT to code, it gives me this...
INSERT INTO [clients].[dbo].[Calculato
([PayCalendar]
,[K-CutOff]
,[PayPeriodsRemaining]
,[PayCalendar2])
VALUES
(<PayCalendar, datetime,>
,<K-CutOff, datetime,>
,<PayPeriodsRemaining, int,>
,<PayCalendar2, datetime,>)
Where and how (the proper syntax) would I enter these to rows from the access database...
PayCalendar / K-CutOff / PayPeriodsRemaining/PayCal
1/7/2005 ....12/29/2004....25....1/
1/21/2005....1/12/2005....
They are formated as date/time and cint....I tried placing in the exported text into the generated insert statement in between the parens, but could not figure it out - below is the format that excell produces based on the export wizard (to a text file)
20050107 0:00:00,20041229 0:00:00,25,20050107 0:00:00
20050121 0:00:00,20050112 0:00:00,24,20050121 0:00:00
I am looking to do the insert statement sucessfully, and have anyone point out any problems if they see them...HELP!!!
I need to insert all of the records from access into the database. When I pull up the ScriptTable as-->INSERT to code, it gives me this...
INSERT INTO [clients].[dbo].[Calculato
([PayCalendar]
,[K-CutOff]
,[PayPeriodsRemaining]
,[PayCalendar2])
VALUES
(<PayCalendar, datetime,>
,<K-CutOff, datetime,>
,<PayPeriodsRemaining, int,>
,<PayCalendar2, datetime,>)
Where and how (the proper syntax) would I enter these to rows from the access database...
PayCalendar / K-CutOff / PayPeriodsRemaining/PayCal
1/7/2005 ....12/29/2004....25....1/
1/21/2005....1/12/2005....
They are formated as date/time and cint....I tried placing in the exported text into the generated insert statement in between the parens, but could not figure it out - below is the format that excell produces based on the export wizard (to a text file)
20050107 0:00:00,20041229 0:00:00,25,20050107 0:00:00
20050121 0:00:00,20050112 0:00:00,24,20050121 0:00:00
I am looking to do the insert statement sucessfully, and have anyone point out any problems if they see them...HELP!!!
ASKER
How do I map to sql - Is ther a way to just export the records as a preformatted insertable value? The export function in excell does not add parenthesis around each record, even when I select ' as the text delimiter.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I object as he said he exported to excel and then imported back into SQL.
My suggestion was to export to txt/csv. This was his main issue of how to get the data from Access to SQL. My solution solved that for him. He went about it a litttle differently but it still involved exporting the data and then inserting into SQL as I descrived above.
My suggestion was to export to txt/csv. This was his main issue of how to get the data from Access to SQL. My solution solved that for him. He went about it a litttle differently but it still involved exporting the data and then inserting into SQL as I descrived above.
ASKER
Look, I will award you the points - but this post is a conceptual response, not an answer
"if you have your mappings from access to SQL already set up you should be able to just do this :
INSERT INTO [clients].[dbo].[Calculato
([PayCalendar]
,[K-CutOff]
,[PayPeriodsRemaining]
,[PayCalendar2])
Select [PayCalendar], [K-CutOff], [PayPeriodsRemaining], [PayCalendar2]
From AccessTableNameHere"
This post is also conceptual - I do not know what a .cvs is (I do now)
"I am sorry from your description it sounded like you already had that linking set up. Since you do not it would be best to do it this way:
From the Access database you would export the file as a flat/comma delimited file and save it as either .txt or .csv on export from Access.
I had already done this prior to the post. This is the only section of the response that involves one of the steps - except it should be excel. To do this as posted does not work - the delimiters do not happen to show up as intended for a SQL Query, and if you have to do 1 or 4000 records will not work with out the additonal formatting.
Then you can use the SQL import wizard to just import the txt/csv file that you exported from Access."
I am not familiar with SQL wizard, and it probably is a better way to do it if you would please explain - I do not think my 2005 SQL Management Compact edition has this tool.
No where do I see any instruction on how to do anything. It would be as if I asked you how to deploy a web file to a server and I had no prior knowledge and your answer was "FTP it" - I would be looking on instructions how to open up the software, make sure the settings were correct, etc. - I am new to working with SQL and the Studio, sorry.
Do you agree?
"if you have your mappings from access to SQL already set up you should be able to just do this :
INSERT INTO [clients].[dbo].[Calculato
([PayCalendar]
,[K-CutOff]
,[PayPeriodsRemaining]
,[PayCalendar2])
Select [PayCalendar], [K-CutOff], [PayPeriodsRemaining], [PayCalendar2]
From AccessTableNameHere"
This post is also conceptual - I do not know what a .cvs is (I do now)
"I am sorry from your description it sounded like you already had that linking set up. Since you do not it would be best to do it this way:
From the Access database you would export the file as a flat/comma delimited file and save it as either .txt or .csv on export from Access.
I had already done this prior to the post. This is the only section of the response that involves one of the steps - except it should be excel. To do this as posted does not work - the delimiters do not happen to show up as intended for a SQL Query, and if you have to do 1 or 4000 records will not work with out the additonal formatting.
Then you can use the SQL import wizard to just import the txt/csv file that you exported from Access."
I am not familiar with SQL wizard, and it probably is a better way to do it if you would please explain - I do not think my 2005 SQL Management Compact edition has this tool.
No where do I see any instruction on how to do anything. It would be as if I asked you how to deploy a web file to a server and I had no prior knowledge and your answer was "FTP it" - I would be looking on instructions how to open up the software, make sure the settings were correct, etc. - I am new to working with SQL and the Studio, sorry.
Do you agree?
ASKER
I awarded you the points. I get frustrated when people who know more than me cannot explain everything lol, sorry. Maybe you could take a look at this one -
https://www.experts-exchange.com/questions/23858868/Accessing-newly-converted-from-Access-SQL-DB-for-ASP-NET-calculator-application.html
https://www.experts-exchange.com/questions/23858868/Accessing-newly-converted-from-Access-SQL-DB-for-ASP-NET-calculator-application.html
Thank You.
I did not go into full 100% detail because I did not know how much you knew. It says you are a beginner but that is it. I was limited on my details because if I went full into details all the time answering questions I would be writing PAGES. If something is not fully detailed and someone says they need more or do not understand something, I go into detail of what part they are referring to.
I may have misread your post but it did not go into some of the details you stated above saying that you already extracted the data to EXCEL, etc. If I would have realized that at the beginning I would have posted something completely different to start.
Sorry for the confusion.
I did not go into full 100% detail because I did not know how much you knew. It says you are a beginner but that is it. I was limited on my details because if I went full into details all the time answering questions I would be writing PAGES. If something is not fully detailed and someone says they need more or do not understand something, I go into detail of what part they are referring to.
I may have misread your post but it did not go into some of the details you stated above saying that you already extracted the data to EXCEL, etc. If I would have realized that at the beginning I would have posted something completely different to start.
Sorry for the confusion.
INSERT INTO [clients].[dbo].[Calculato
([PayCalendar]
,[K-CutOff]
,[PayPeriodsRemaining]
,[PayCalendar2])
Select [PayCalendar], [K-CutOff], [PayPeriodsRemaining], [PayCalendar2]
From AccessTableNameHere