Solved

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

Posted on 2008-10-29
8
666 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now