February 1, 2008 at 6:29 pm
I need to do bulk insert from the text file into a table where I have id int, datetime field, etc.
I get an error "type mismatch" probably because everything in the text file is a character string and
"Each field in the data file must be compatible with the corresponding column in the target table. For example, an int field cannot be loaded into a datetime column. "
So what should I do? Create some additional table where all fields are char type and bulk insert there and after that copy and convert type from that table into my real table? :crying:
February 1, 2008 at 10:14 pm
If your source data is that messy then that's probably the easiest thing to do.
February 2, 2008 at 8:28 am
Hard to tell from here... you didn't post any piece of the source file. Recommend you attach the first 100 lines of the text file and post the create statement for the table you're trying to import into. Be sure to identify what the record layout is for the text file.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2008 at 9:29 am
Can I do bulk insert into a table variable or I can only bulkinsert into a real table?
DECLARE @myTable TABLE(
[id] [varchar](max) ,
[empl_name] [varchar](max) ,
[empl_id] [varchar](max) ULL,
[date] [varchar](max)LL,
[punch_in] [varchar](max) ,
[punch_out] [varchar](max)
)
BULK INSERT @myTable
FROM 'C:\Documents and Settings\myFile.txt'
with (FIELDTERMINATOR = '|' )
February 4, 2008 at 11:20 am
I usually bulk insert into a "real" table as you call them. Usually a "copy" of the real table (empty table with same DDL structure as the real one), just so as to shake out any data typing/scrubbing issues. Of course - if there are any issues - then I would switch the data types back to something text-related (like you seem to be doing), so as to get past any data casting issues.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2008 at 11:52 am
I faced the same issue.So While setting the source connection I used a format file (.fmt) created from the SQLtable. Also if the text file has a heading then the first row should start from row 2.
February 4, 2008 at 11:58 am
OK you are right. It is not possible to bulk insert from the file into a table variable. It is possible to bulk insert from the file into a temporary table. All fields in the temporary table are char or varchar so there is no datatype mismatch etc. Then I copy data from temporary table into a table variable. Table variable has correct data types like numeric, datetime etc and when I copy to a table variable from temp table there is no data type mismatch. It's just takes two steps instead of one.:P
February 4, 2008 at 12:53 pm
It doesn't allow you to bulk insert from a file if file name is a variable.
Declare @path varchar
@path = 'file_02042008.txt'
BULK INSERT myTable
FROM @path
with (FIELDTERMINATOR = '|' )
So bulk insert doesn't allow to insert from a file if file name is a variable and doesn't allow to insert into a table if it's a table variable. Bulk insert is bad, not a flexible at all!
February 4, 2008 at 2:01 pm
Just about any SELECT statement would fall apart with that syntax, since you're trying (incorrectly) to use what is called "dynamic SQL".
Here's an example of what you're looking to do:
declare @fname as varchar(100)
select @fname='c:\temp\pipes.txt'
declare @sql varchar(500)
select @sql='BULK INSERT myTable FROM ''';
select @sql=@sql+ @fname ;
select @sql=@sql+''' with (FIELDTERMINATOR = ''|'' )';
print @sql
exec(@sql)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2008 at 2:22 pm
Yes I realized that. Thanks
February 13, 2008 at 3:54 pm
Can I use table variable with the dynamic SQL?
February 13, 2008 at 5:51 pm
A table variable created outside of the dynamic sql is not visible within the dynamic sql. You can make and use the table variable within the dynamic sql.
declare @fname as varchar(100)
select @fname='c:\temp\pipes.txt'
declare @sql varchar(500)
select @sql='BULK INSERT myTable FROM ''';
select @sql=@sql+ @fname ;
select @sql=@sql+''' with (FIELDTERMINATOR = ''|'' )';
select @sql=@sql+ ' declare @test-2 table (qaz varchar(10)) insert into @test-2 select whatsis from myTable '
print @sql
exec(@sql)
February 13, 2008 at 6:27 pm
Vika (2/4/2008)
OK you are right. It is not possible to bulk insert from the file into a table variable. It is possible to bulk insert from the file into a temporary table. All fields in the temporary table are char or varchar so there is no datatype mismatch etc. Then I copy data from temporary table into a table variable. Table variable has correct data types like numeric, datetime etc and when I copy to a table variable from temp table there is no data type mismatch. It's just takes two steps instead of one.:P
Oh.... speaking from some bit of recent experience... be real careful about using Bulk Insert on Temp Tables... they may work fine when you run them from Query Analyzer, but when they run as a stored proc under some other user name, that login may not have the privs to do it. You may have to SET ARITHABORT ON to get the Bulk Insert to work on a Temp Table and if the user the proc is running under doesn't have the privs to do that in TempDB... BOOOOM!
Also, I'd never copy from a Temp Table into a Table variable... no advantage to it... both live in memory if there's room, both live in TempDB if there's not. Table variables do not and cannot be made to use statistics, so they can get pretty slow...
That's not just me talking, either... pay real close attention to Q3/A3 and Q4/A4 in the following article from Microsoft... (yeah, I know, it's a 2k article...)
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
Also, the following URL has some great test code made by the wireheads at Microsoft...
I especially like the explanation about Temp Table usage and recompiles... blows most of that myth away... and I quote... I've highlighted the "Myth Buster" part that I really like...
When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled. This is because during initial compilation, the temporary table does not exist and the compilation of this query is deferred until execution time. A compiled plan for the stored procedure is generated, but is incomplete. At execution time, the temporary table is created, and the select and insert statement are compiled. Since the stored procedure is already in execution, this compilation of the select and insert query are classified as a recompilation. It is important to note that in SQL Server 2005, only the select and insert statement in the stored procedure are recompiled. In SQL Server 2000, the entire stored procedure is recompiled. Subsequent re-executions of this stored procedure do not result in any more recompiles since the compiled plan is cached. Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used. Now consider a case when the temporary table is referenced in a second stored procedure as below:
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 8:53 am
Jeff,
I am really confused now. Yes, I use bulk insert into the temp table and I copy it into table variable and then drop temporary table and I do it in the stored procedure. It's OK, data import works OK.
But then I use import for reports in the reporting services and I wrote a lot of stored procedures.
My co-worker looked at my code and yelled at me "Never use dynamic SQL! It's bad! Never use temporary tables! it's bad!" blah-blah. He printed several articles and threw it on my desk. the title was something like "The curse of dynamic SQL!", "Eliminate use of Temporary Tables! "
He scared me, he sad if 50 people run a report at the same time, 50 temporary tables created. So I tried to re-write all those reports, get rid of temporary tables and put everything into the table variables, that means to put those table variables into the scope of dynamic SQL. It's a lot of work, it's like re-writing everything all over again almost from scratch. And I HAVE to use dynamic SQL because SP takes parameters and depending on the parameters queries can be completely different.
Now I don't know .. is replacing of temporary tables with table variables worth it or not?
I just try to think about good programming style as well as SQL server performance, memory...
February 14, 2008 at 9:25 am
Vika - Jeff's point about temporary tables versus table variables is that they're really not all that different. Most of the things your colleague was trying to scare you with would apply equally to a table variable as they would to a temp table. They both exist in memory if they fit, get flushed to tempdb if need be, etc...
Except that table variables cannot be indexed or use statistics, so if you are reusing in something with heavy joining, etc... it won't run as fast, so the table variable lives in memory (or tempDB) longer.
And - all due respect to your colleague - those are cheap scare tactics that don't apply to all situations. If you use temp tables intelligently, they won't be around long enough for them to be a problem (same with dynamic SQL). Like pretty much anything else - you can use it well and it will help you, or you can use it stupidly and it will blow up in your face. You just have to take those warnings in stride, and use them wisely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply