Solved

Insert date/time records from access into a sql database.

Posted on 2008-10-29
8
671 Views
Last Modified: 2012-05-05
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].[Calculator]
           ([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/PayCalendar2
1/7/2005 ....12/29/2004....25....1/7/2005
1/21/2005....1/12/2005....24.....1/21/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!!!

0
Comment
Question by:theclassic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22831378
if you have your mappings from access to SQL already set up you should be able to just do this :

INSERT INTO [clients].[dbo].[Calculator]
           ([PayCalendar]
           ,[K-CutOff]
           ,[PayPeriodsRemaining]
           ,[PayCalendar2])
Select [PayCalendar], [K-CutOff], [PayPeriodsRemaining], [PayCalendar2]
From AccessTableNameHere
0
 

Author Comment

by:theclassic
ID: 22831718
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.
0
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 500 total points
ID: 22831919
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.

Then you can use the SQL import wizard to just import the txt/csv file that you exported from Access.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
theclassic earned 0 total points
ID: 22832592
I exported to excell, added the syntax characters and statements needed to other adjoining cells, copied and pasted into word (as if you paste it right into the query script in SQL Management Studio 2005, it places " in front of each statement) and then copied and pasted from word into the SQL script and hit execute...
INSERT INTO [clients].[dbo].[Calculator]
           ([PayCalendar]
           ,[K-CutOff]
           ,[PayPeriodsRemaining]
           ,[PayCalendar2])
     VALUES ( '20050107', '20041229', '25', '20050107' )
INSERT INTO [clients].[dbo].[Calculator]
           ([PayCalendar]
           ,[K-CutOff]
           ,[PayPeriodsRemaining]
           ,[PayCalendar2])
     VALUES ( '20050121', '20050112', '24', '20050121' ) etc.... and then at the end a ;
 
Thanks
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22832707
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.
0
 

Author Comment

by:theclassic
ID: 22833576
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].[Calculator]
           ([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?  
0
 

Author Comment

by:theclassic
ID: 22833681
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 -
 
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23858868.html 
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22834181
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question