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 |
|
kotonikak
Yak Posting Veteran
92 Posts |
 Posted - 2012-08-30 : 15:49:48
|
| I'm creating a stored procedure with 4 parameters which are 4 different data files. I want to do this because I'm picking the data files in a command button from VBA and I want it to execute the procedure via macro. I have the following SQL code for one datafile:DECLARE @sql nvarchar(4000) select @sql = 'Bulk Insert AnalyticsV2.CM.InterestRate' + 'FROM ' + char(39) + @InterestRate + CHAR(39)+ ' WITH (DATAFILETYPE='char',FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')' exec sp_executesql @sql;The thing is that my InterestRate table has 3 columns with 3 different datatypes:DateofRate (date)TermbyMonth (smallint)InterestRate (real)I'm sure the DATAFILETYPE is not correct in this procedure but how do I fix this so I can actually import my csv file?Any help will be appreciated. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-30 : 16:02:18
|
| so far as your dates are in consistent unambiguos format you wont have any issues in inserting as they will undergo implicit conversions to target column datatypeshowever if you do have to do an explicit convert use OPENROWSET or dump above results to staging table and do a select from there to final table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-08-30 : 16:05:53
|
| Did you ever test it? IF so, What is the error?--------------------------Joins are what RDBMS's do for a living |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2012-08-30 : 16:12:24
|
| When I tested the code mentioned in visual basic, I get an incorrect syntax near the file path. When I put the code in SQL, I get an incorrect syntax near 'char' (char is underlined in red) I'm not quite sure how to convert using OPENROWSET but I'm looking into it since I have never used it before... |
|
|
|
kotonikak
Yak Posting Veteran
92 Posts |
Posted - 2012-08-30 : 16:17:06
|
| My bad...I'm not actually testing this same code in visual basic...I meant the connection and command to run this code in SQL. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-31 : 15:06:04
|
| whats the datatype of @InterestRate? Also how are you passing value for it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Bulk Insert for .csv file