Import Access tables to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import Access tables to SQL

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-11-16 : 05:34:07

I have some Access tables that I want to import into SQL. I can do this using the AllTasks/ImportData. However if my Access table has a zero length string in a field this is imported into the SQL table as <NULL>. How can I make it import it as a zero length string ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:43:37

Why do you worry about NULLs. You can handle it very well in front end application or in sql
One simple way is update table after importing data

Update table
Set col='' where col is NULL

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-16 : 05:57:18

In Transformation dialog box, Click on "Transform information as it is copied to the destination" radio button and edit the script as:

Function Main()
	....
	....
	if IsNull(DTSSource("col")) then
		DTSDestination("col") = ""
	else
		DTSDestination("col") = DTSSource("col")
	end if
	....
	...
	Main = DTSTransformStat_OK
End Function

for all the desired columns.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources