Importing data to tables - 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 Administration

Importing data to tables

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-04 : 11:22:54

I have to import data from csv file to a table in SQL server 2005

The Datatype for a specific column in the flat file is "Charecter'
The Datatype of the column to which it needs to get imported in the database is nvarchar

I have done it multiple times and see lot of records missing after importing to the table

can someone please advise what is going wrong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:59:23

how are you doing upload currently? using wizard or programatically?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-04 : 16:26:17

Iam Doing using the Import Wizard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:20:18

can you try chnaging data type in advance editor of flat file source?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-05-05 : 04:12:24

I import into a temporary "staging" table will all columns set to Varchar/Nvarchar and massively wider than they anticipated data, I can then do tests like


SELECT CASE WHEN LEN(Col1) <= 200 THEN '' ELSE 'Col1 >200 char. ' END
     + CASE WHEN IsDate(Col2) = 1 THEN '' ELSE 'Col2 not a valid date. ' END AS [ErrorMessage],
     *
FROM MyStagingTable

to find where columns are too wide for the target table, etc.

Then when I've cleaned up the mess

I can do


INSERT INTO MyTargetTable
SELECT *
FROM MyStagingTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 06:33:53

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2009/10/19/finding-out-problematic-data-in-bulk-insert-data-truncation-error.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources