November 6, 2008 at 9:05 am
I do a BULK insert inside a stored procedure that takes a parameter for the filepath. The file used for the INSERT is .CSV, comma delimited. Each field value in the .csv file is wrapped with "" (double quotes) too. For example, "dog", "cat", "mouse",. During the BULK INSERT the first " is removed. I needed to remove the 2nd " so I added the Cursor code below. All of this works.
What I now need to do is update a date field in the my_table table that is not a field in the .csv file passed in. It's a date field, the date the row was inserted into my_table.
I am new to BULK inserts, in fact the below is my first one. How can I set the value of my_table.dateposted from this stored procedure? Is there a way to tell the table.column to auto generate this date value when the row is inserted?
Thanks
ALTER PROCEDURE [dbo].[spr_Bulk_Insert_my_table]
@FilePathAndName varchar(255)
AS
BEGIN
DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)
SET NOCOUNT ON;
SET @sql = 'BULK INSERT dbo.my_table FROM ''' + @FilePathAndName + ''' WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ''",''
)'
EXECUTE sp_executesql @sql
SET @sql = ''
DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl, c.name col
FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
where o.name = 'my_table'
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN
SET @sql =
'UPDATE dbo.' + @tbl + '
SET ' + QUOTENAME(@col) + ' = RIGHT(LTRIM(RTRIM(' + QUOTENAME(@col) + ')), LEN(LTRIM(RTRIM(' + QUOTENAME(@col) + '))) - 1)
WHERE LEFT(LTRIM(RTRIM(' + QUOTENAME(@col) + ')), 1) = ''"'' '
EXECUTE sp_executesql @sql
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1
END
November 6, 2008 at 9:13 am
bulk insert can handle comma delimited, + quote delimited data automatically, but you must use a format file.
see this blog for an example:
http://lanestechblog.blogspot.com/2008/08/sql-server-bulk-insert-using-format.html
that way there is no need to clean the data, as it comes in perfect on the first pass.
your command would be something like this:
bulk INSERT temptable FROM 'csvfilename' WITH (FIRSTROW = 2, FORMATFILE='myformatfilename.txt')
Lowell
November 6, 2008 at 9:37 am
Thanks Lowell. Any idea how I can set the date field in the SQL Table where the value is not in the .csv file?
November 6, 2008 at 4:36 pm
i typically import into a staging table first, and then into the actual data; that way i can use something like this:
insert into FinalDestination(column list ....)
select
ltrim(rtrim(somefield)),
coalesce(somedatetimefield2,getdate()),
coalesce(somemoneyfield,0.00)
from StagingTable
that lets me do all the validation and swapping i need to do from staging to final
Lowell
November 10, 2008 at 5:55 am
That's what I ended up doing, works perfect, thanks.
November 10, 2008 at 2:36 pm
IF you don't need to validate etc you should use format files to handle this kind of things
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply