November 16, 2015 at 9:26 am
I have a text file that I receive from a partner. The file gets imported into our SQL using a temp table in a SQL Job. The process is we create a temp table, then bulk insert the text file into the temp file and then into the SQL table. Here is what it looks like:
CREATE TABLE #apt_mkdn(
[PJ_RES_ID_PRG] [int] NULL,
[PJ_REBATE_ID] [int] NULL,
[PJ_REBATE_DATE] [datetime] NULL
)
bulk insert #apt_mkdn from '\markdown.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR='0x0a', FIRSTROW=2)
go
insert into PROJECT_INFO(PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE)
SELECT
PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE FROM #apt_mkdn
go
drop table #apt_mkdn
go
In the table PROJECT_INFO there is a field for import date. What I need to do is to add this date using getdate() or similar function to the table for all of the records that have just been inserted. I hope this makes sense.
I have tried to add importdate to both the insert statement and the select statement like
insert into PROJECT_INFO(PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE, ImportDate)
SELECT
PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE, getdate() FROM #apt_mkdn
go
I get errors each time.
November 16, 2015 at 9:44 am
vavfam 50321 (11/16/2015)
I have a text file that I receive from a partner. The file gets imported into our SQL using a temp table in a SQL Job. The process is we create a temp table, then bulk insert the text file into the temp file and then into the SQL table. Here is what it looks like:
CREATE TABLE #apt_mkdn(
[PJ_RES_ID_PRG] [int] NULL,
[PJ_REBATE_ID] [int] NULL,
[PJ_REBATE_DATE] [datetime] NULL
)
bulk insert #apt_mkdn from '\markdown.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR='0x0a', FIRSTROW=2)
go
insert into PROJECT_INFO(PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE)
SELECT
PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE FROM #apt_mkdn
go
drop table #apt_mkdn
goIn the table PROJECT_INFO there is a field for import date. What I need to do is to add this date using getdate() or similar function to the table for all of the records that have just been inserted. I hope this makes sense.
I have tried to add importdate to both the insert statement and the select statement like
insert into PROJECT_INFO(PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE, ImportDate)
SELECT
PJ_RES_ID_PRG,PJ_REBATE_ID,PJ_REBATE_DATE, getdate() FROM #apt_mkdn
goI get errors each time.
Simple stating you "get errors" does not help. What are the errors?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2015 at 9:48 am
Here is the error I get. I also max out on errors and it fails at that point.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 39 (ImportDate). [SQLSTATE 42000]
November 16, 2015 at 9:52 am
vavfam 50321 (11/16/2015)
Here is the error I get. I also max out on errors and it fails at that point.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 39 (ImportDate). [SQLSTATE 42000]
That is a pretty clear indication you are trying to insert into a date/datetime column and the source data has an invalid value.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2015 at 9:58 am
I am trying to insert the current date using getdate() into a field in the table that is datetime.
I am not aware of how I would have to change the getdate() function to produce something that will allow me to insert it other than just calling it. All I want to do is to get the current date into the table for each record that is being loaded. It does not exist in the text file so I need to add it at import time.
November 16, 2015 at 10:14 am
My solution would be to add a default constraint to get the current date on the column. With that in place, use a format file to insert values only in the first 2 columns. For guidance on how to do it, check the following article: http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
November 16, 2015 at 10:18 am
Luis Cazares (11/16/2015)
My solution would be to add a default constraint to get the current date on the column. With that in place, use a format file to insert values only in the first 2 columns. For guidance on how to do it, check the following article: http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
I do this exact thing in more places than I even care to count. It's a tried-and-true approach for me and I'd recommend it to anyone.
November 16, 2015 at 10:24 am
vavfam 50321 (11/16/2015)
Here is the error I get. I also max out on errors and it fails at that point.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 39 (ImportDate). [SQLSTATE 42000]
Looking at your code and the error message I'd say the problem is with your BULK INSERT, not the code that follows. Of course we can't prove that from here, so you need should run each step of the process you posted one at a time to know for sure where the error is occurring.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy