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.
| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2011-02-09 : 09:54:17
|
| Hi i Have this stored procedure that imports data in to the database. i didn't write this SP i can't seem to find where I should put the file path to where the data is going to come from. I thought it would go here : @p_folderpath varchar(250)right in the middle of @p_folderpath and varchar 250? am i missing something? USE [SCP]GO/****** Object: StoredProcedure [dbo].[sp_ImportData] Script Date: 02/09/2011 09:52:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[sp_ImportData]@p_folderpath varchar(250)/** This proc imports data for the SCP database using bulk insert.** Params:* @p_folderpath - Pass in the directory name where the bulk insert files are located. All files must* be in the folder. Folder should be in this format (no trailing "\"):* c:\myfolder\mydir** This proc will:* - Remove all existing data in the target tables via truncate * - Import all new data in the target tables via bulk insert* - for a list of tables that will be affected, look at the proc below and note the tables in the temporary table #t - those are the tables worked w/ in this operation** Assumptions:* - Table structures for SCP are created and schema is aligned with the bulk insert files* - Folder Path passed is a local path and is accessible by the sql process sid*/as create table #t ( tablename varchar(50) not null ) insert into #t select '_grade_level_mtd' insert into #t select '_MAA_Dist' insert into #t select '_MAA_Jobs' insert into #t select '_MAA_People' insert into #t select '_MAA_Schs' insert into #t select '_prefix_mtd' insert into #t select '_program_type_mtd' insert into #t select '_school_type_mtd' insert into #t select '_suffix_mtd' insert into #t select 'address' insert into #t select 'address_type' insert into #t select 'book_series_type' insert into #t select 'call_day' insert into #t select 'district' insert into #t select 'entry_type' insert into #t select 'fractional_accessory_type' insert into #t select 'fractional_brand' insert into #t select 'fractional_product' insert into #t select 'grade_level' insert into #t select 'instrument_class' insert into #t select 'instrument_class_brand' insert into #t select 'instrument_type' insert into #t select 'interaction_type' insert into #t select 'location' insert into #t select 'note_type' insert into #t select 'preference' insert into #t select 'preference_bookseries' insert into #t select 'preference_bookseries_catg' insert into #t select 'preference_fractional_item' insert into #t select 'preference_global_item' insert into #t select 'preference_instrument' insert into #t select 'preference_instrument_brand' insert into #t select 'preference_instrument_item' insert into #t select 'preference_item' insert into #t select 'preference_item_default' insert into #t select 'preference_item_type' insert into #t select 'prefix' insert into #t select 'program' insert into #t select 'program_teacher' insert into #t select 'program_type' insert into #t select 'rate_code' insert into #t select 'rate_package' insert into #t select 'rate_package_default' insert into #t select 'relationshiptype' insert into #t select 'relationshipmanager' insert into #t select 'rental_transaction_type' insert into #t select 'routine_service' insert into #t select 'school' insert into #t select 'school_type' insert into #t select 'scp_note' insert into #t select 'scp_type' insert into #t select 'suffix' insert into #t select 'teacher' insert into #t select 'vendor_relationship' insert into #t select 'catg' insert into #t select 'mfg' insert into #t select 'prod' insert into #t select 'prodoption' declare @cmd varchar(max) declare @tablename varchar(50) declare t_cursor cursor for select tablename from #t open t_cursor fetch next from t_cursor into @tablename while @@FETCH_STATUS = 0 BEGIN Print 'Removing data from table ' + @tablename + '...' set @cmd = 'truncate table ' + @tablename exec(@cmd) Print 'Importing new data for table ' + @tablename + '...' set @cmd = 'bulk insert ' + @tablename + ' from ''' + @p_folderpath + '\' + @tablename + '.txt'' with ( fieldterminator = ''~'', keepidentity )' print @cmd exec(@cmd) fetch next from t_cursor into @tablename END close t_cursor deallocate t_cursor drop table #t |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-09 : 10:08:16
|
For example:exec sp_ImportData 'c:\myfolder\mydir'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-02-09 : 10:37:06
|
| Thanks for that but i got WHOLE lot of errors any help there?(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)Removing data from table _grade_level_mtd...Importing new data for table _grade_level_mtd...bulk insert _grade_level_mtd from 'C:\jbs daTA\_grade_level_mtd.txt' with ( fieldterminator = '~', keepidentity )(8 row(s) affected)Removing data from table _MAA_Dist...Importing new data for table _MAA_Dist...bulk insert _MAA_Dist from 'C:\jbs daTA\_MAA_Dist.txt' with ( fieldterminator = '~', keepidentity )(9662 row(s) affected)Removing data from table _MAA_Jobs...Importing new data for table _MAA_Jobs...bulk insert _MAA_Jobs from 'C:\jbs daTA\_MAA_Jobs.txt' with ( fieldterminator = '~', keepidentity )(356323 row(s) affected)Removing data from table _MAA_People...Importing new data for table _MAA_People...bulk insert _MAA_People from 'C:\jbs daTA\_MAA_People.txt' with ( fieldterminator = '~', keepidentity )(103453 row(s) affected)Removing data from table _MAA_Schs...Importing new data for table _MAA_Schs...bulk insert _MAA_Schs from 'C:\jbs daTA\_MAA_Schs.txt' with ( fieldterminator = '~', keepidentity )(82990 row(s) affected)Removing data from table _prefix_mtd...Importing new data for table _prefix_mtd...bulk insert _prefix_mtd from 'C:\jbs daTA\_prefix_mtd.txt' with ( fieldterminator = '~', keepidentity )(23 row(s) affected)Removing data from table _program_type_mtd...Importing new data for table _program_type_mtd...bulk insert _program_type_mtd from 'C:\jbs daTA\_program_type_mtd.txt' with ( fieldterminator = '~', keepidentity )(47 row(s) affected)Removing data from table _school_type_mtd...Importing new data for table _school_type_mtd...bulk insert _school_type_mtd from 'C:\jbs daTA\_school_type_mtd.txt' with ( fieldterminator = '~', keepidentity )(9 row(s) affected)Removing data from table _suffix_mtd...Importing new data for table _suffix_mtd...bulk insert _suffix_mtd from 'C:\jbs daTA\_suffix_mtd.txt' with ( fieldterminator = '~', keepidentity )(17 row(s) affected)Removing data from table address...Importing new data for table address...bulk insert address from 'C:\jbs daTA\address.txt' with ( fieldterminator = '~', keepidentity )(267849 row(s) affected)Removing data from table address_type...Importing new data for table address_type...bulk insert address_type from 'C:\jbs daTA\address_type.txt' with ( fieldterminator = '~', keepidentity )(2 row(s) affected)Removing data from table book_series_type...Importing new data for table book_series_type...bulk insert book_series_type from 'C:\jbs daTA\book_series_type.txt' with ( fieldterminator = '~', keepidentity )(4 row(s) affected)Removing data from table call_day...Importing new data for table call_day...bulk insert call_day from 'C:\jbs daTA\call_day.txt' with ( fieldterminator = '~', keepidentity )(5 row(s) affected)Removing data from table district...Importing new data for table district...bulk insert district from 'C:\jbs daTA\district.txt' with ( fieldterminator = '~', keepidentity )(8860 row(s) affected)Removing data from table entry_type...Importing new data for table entry_type...bulk insert entry_type from 'C:\jbs daTA\entry_type.txt' with ( fieldterminator = '~', keepidentity )Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 3 (CreateDate).Removing data from table fractional_accessory_type...Importing new data for table fractional_accessory_type...bulk insert fractional_accessory_type from 'C:\jbs daTA\fractional_accessory_type.txt' with ( fieldterminator = '~', keepidentity )(2 row(s) affected)Removing data from table fractional_brand...Importing new data for table fractional_brand...bulk insert fractional_brand from 'C:\jbs daTA\fractional_brand.txt' with ( fieldterminator = '~', keepidentity )(7 row(s) affected)Removing data from table fractional_product...Importing new data for table fractional_product...bulk insert fractional_product from 'C:\jbs daTA\fractional_product.txt' with ( fieldterminator = '~', keepidentity )(106 row(s) affected)Removing data from table grade_level...Importing new data for table grade_level...bulk insert grade_level from 'C:\jbs daTA\grade_level.txt' with ( fieldterminator = '~', keepidentity )(5 row(s) affected)Removing data from table instrument_class...Importing new data for table instrument_class...bulk insert instrument_class from 'C:\jbs daTA\instrument_class.txt' with ( fieldterminator = '~', keepidentity )(19 row(s) affected)Removing data from table instrument_class_brand...Importing new data for table instrument_class_brand...bulk insert instrument_class_brand from 'C:\jbs daTA\instrument_class_brand.txt' with ( fieldterminator = '~', keepidentity )(91 row(s) affected)Removing data from table instrument_type...Importing new data for table instrument_type...bulk insert instrument_type from 'C:\jbs daTA\instrument_type.txt' with ( fieldterminator = '~', keepidentity )(57 row(s) affected)Removing data from table interaction_type...Importing new data for table interaction_type...bulk insert interaction_type from 'C:\jbs daTA\interaction_type.txt' with ( fieldterminator = '~', keepidentity )Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (CreateDate).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (CreateDate).Removing data from table location...Importing new data for table location...bulk insert location from 'C:\jbs daTA\location.txt' with ( fieldterminator = '~', keepidentity )Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 72 (Latitude).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 72 (Latitude).Msg 4865, Level 16, State 1, Line 1Cannot bulk load because the maximum number of errors (10) was exceeded.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-09 : 11:36:53
|
| seems that you are trying to insert invalid data into a wrong datatype(column)... check the data in the corresponding file and the structure of the table ... and try to perform bulk insert only for the files that are giving you errors |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-02-14 : 09:45:46
|
| thank you. I fixed that portion of it. The data we were getting from JBS was wrong. he sent a new data feed this morning. did the import now getting a diffrent error. never seen these before. emoving data from table preference...Importing new data for table preference...bulk insert preference from 'C:\jbs daTA\preference.txt' with ( fieldterminator = '~', keepidentity )Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 26 (ishidden).Msg 4864, Level 16, State 1, Line 1Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 26 (ishidden).Msg 4865, Level 16, State 1, Line 1Cannot bulk load because the maximum number of errors (10) was exceeded.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2011-02-14 : 09:47:19
|
| the data type for the column (ishidden) is bit, is that what the problem could be? |
|
|
|
|
|
|
|
|
Importing Data