Importing data from Access table to SQL Table

Question

Importing data from Access table to SQL Table

Michael Whittinghill

Grasshopper

Points: 19
More actions
February 2, 2006 at 12:37 pm

#76482

I have created a DTS package that imports data from an access table to a SQL table and I have a problem with two of the columns. One is a timestamp column in the SQL table. I need to put a timestamp value in the field but am not able to do so. The other is a identity column (int). This column contains a unique number. The first record starts with 1 and goes from there. How can I get the pacjage to increment from the last number or somehow get a unique number that won't be duplicated. Any help would be appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Michael,

You can't enter any value in the timestamp datatype column. SQL Server automatically generates binary numbers which are guaranteed to be unique within a database.

Coming to Identity column. There is no need to enter any value, SQL automatically increments the column value for a column specified with identity.

Thanks and have a nice day!!!

Lucky

Michael Whittinghill Grasshopper Points: 19 More actions · Answer 2

What I am asking is how do I handle these columns in the DTS package. The ways that I have tried including deleting the columns from the package have ended either in errors or posting bad data.

Ian Yates SSCoach Points: 19738 More actions · Answer 3

What do you mean by "bad data"? Not the values you expect? Certainly the timestamp will be different... And the identity column will get values in the order in which the rows are presented to DTS - which may not match the order you desire...

Michael Whittinghill Grasshopper Points: 19 More actions · Answer 4

If I do not remove those fields from the package the identity column has a 0 and the timestamp field has a Null value. If I do remove them the package errors out

Ian Yates SSCoach Points: 19738 More actions · Answer 5

I hate to ask, but could you please supply the error message you are receiving??

Perhaps also run SQL Profiler whilst executing your package and see what statements are being executed... Post the statements here...

Japie Botma SSCrazy Points: 2990 More actions · Answer 6

I believe the best way to get data into sql is to import it first into a temp table with no key and all the columns allowing nulls.

Now use a stored procedure to update your live table.

Truncating the temp table at the beginning of your DTS package helps when you have to do changes, because the last data is allways there.

5ilverFox
Consulting DBA / Developer
South Africa

Giles McArdell SSC Eights! Points: 875 More actions · Answer 7

A Timestamp column is 8 Bytes, so you should be able to copy it into a BINARY(8) column, remember that it will no longer be guaranteed unique though.

I have not entirely understood what problem you have with the Identity column. If you are defining the destination column as identity you will get an error if you try to insert or update it (because it is automatically maintained by the server) . Your options are :-

A) Define the destination column as INT without the identity, copy it from the access table but ensure you use something like - 'WHERE MyAccessTable.MyIdentityColumn NOT IN(SELECT MySQLTable.MyINTColumn FROM MySQLTable)' - in order to maintain only unique rows.

B) Define the destination column as INT with the identity, copy all the columns except the identity column. In this case the server will add in the identity number for you, but it is unlikely to match the numbers in the source column.

Hope this helps

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 8

I don't know anything about time stamps, but importing an identity field is easy. Map the identity field from the Access DB to the identity field in SQL Server, then go to the 4th tab on the properties and check the Enable Identity Insert check box. This will maintain the same id numbers from the Access DB, but still allow SQL to create new unique ones if records are added to the SQL table.

Shalom!,

Michael Lee

Giles McArdell SSC Eights! Points: 875 More actions · Answer 9

Oh yeah, I forgot about that ... D`OH

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

dtevlin Ten Centuries Points: 1084 More actions · Answer 10

To sum up your options:

“One is a timestamp column in the SQL table. I need to put a timestamp value in the field but am not able to do so.”

--You will not be able to insert values into a timestamp column of your choosing from another table. If you have time values in the access table you need to insert into the sql table then follow Giles McArdell's advice and map the time values you have in the access table to a BINARY(8) column. Otherwise if you want to insert a time stamp value of when the data was inserted into the SQL table from the Access table set the default value of the timestamp column to GETDATE() and when you inert data from the Access table in the SQL table the timestamp column will automatically populate with the current time the insert occurred. This auto-populating timestamp column in the SQL table would then not be mapped to any column in the Access table.

“The other is a identity column (int). This column contains a unique number. The first record starts with 1 and goes from there. How can I get the pacjage to increment from the last number or somehow get a unique number that won't be duplicated.”

--If you just need a unique number for the table you options are:

Follow Michael Lee’s advice and use the Enable Identity check box.
In the SQL table create an id column (don’t use id as the label though it is a reserved keyword- well you really could use it just remember that you would have to put double quotes ”id” around it in all you Sql queries against the table) set the seed to 1 and Identity Increment to 1 in the design view of the table in Enterprise Manager. If you choose this then you do not map the identity column from the Access table to the SQL Table. The id column will auto populate and increment itself.

This will work great if this is a one-time DTS package run. However, if you need to import data regularly then I suggest creating a staging table to load the Access data to before importing the data from the staging table to the destination table. The staging table would match the destination table column for column. The DTS package would import the data to the staging table then an Execute SQL Task step would be next that contained an INSERT INTO statement with the WHERE clause that Giles McArdell outlined above. After the insert Execute SQL Task the next step would be to delete all the rows in the staging table so that it would be ready for the next time the package is run so a final Execute SQL Task step could be used.