Solved

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

Posted on 2008-10-29
8
669 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 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