Solved

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

Posted on 2008-10-29
8
668 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

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.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

16 Experts available now in Live!

Get 1:1 Help Now