|
Just an observation
We found that the BCP.exe in SQL 2008 was a lot more unstable than earlier versions. Apparently MS tightened up some of the formatting rules and it basically broke BCP. If you can make it behave then it is still the fastest load method.
We do not do any tansformation during the load, shove everything into varchar fields in a staging table. We then use stored procs to do the transforms. This gives us a number of advantages:
No errors in the load
Easier to manage the errors in the data when it is right there in front of you.
Dramatically faster - I mean hugely faster than either SSIS or Biztalk.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
nooeee.... I have no time to play around or experiment - and how do you implement "Stored procs to do transforms"?
Thanks
dev
|
|
|
|
|
You know what the data looks like and you know what/where it needs to be. So the staging tables have the original data, simply write an insert statement with the transformations and lookups (joins) to move it to your final format.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hey, I got it working - problem with bcp format file
if you follow MSDN advice and mark a field SQLINT (corresponds to database table col type), you'd end up having this int to ASCII conversion problem.
Just mark the damn field as SQLCHAR, and let db do conversion and all good.
dev
|
|
|
|
|
devvvy wrote: Just mark the damn field as SQLCHAR
Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Can we skip format file (-f switch) at all?
I got it working by indicating column tye SQLVAR instead of SQLINT and let SQL do conversion herself - however it's still ridiculous having (mandatory) to specify format file...?
Thanks
dev
|
|
|
|
|
Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?)
C:\somedir\bcp.exe DEV.dbo.SomeMixedData in C:\somedir\output\output_abc_mixed.txt -n -S SomeSErver -T
Starting copy...
0 rows copied. << Just can't get it working without format file.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
When open with Notepad my sample file resembles:
20100701,1.110000e+000,2.220000e+000
20100702,3.330000e+000,4.440000e+000
20100703,5.550000e+000,6.660000e+000
And my simple table:
CREATE TABLE [dbo].[SomeData](
[Col1] [varchar](255) NULL,
[Col2] [varchar](255) NULL,
[Col3] [varchar](255) NULL
)
dev
modified on Tuesday, July 13, 2010 7:02 AM
|
|
|
|
|
Hi All,
How to get sum of a column upto the current row for each row into a different column in SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Hi,
There's multiple ways of calculating a running total. This article[^] shows three of those solutions.
I are Troll 
|
|
|
|
|
|
Not a full solution, but a step towards it:
Let us assume a table "Table1" with following columns and values:
ID val total
1 12 <NULL>
2 23 <NULL>
3 2 <NULL>
4 14 <NULL>
Now you can update the "total" column for each row with a query like the one below for ID=3:
UPDATE table1
SET total = tmp.result
FROM (SELECT MAX(ID) AS maxid, SUM(val) AS result
FROM Table1
WHERE (ID <= 3)) TMP
WHERE table1.id = tmp.maxid
Next, replace "3" by a parameter and run the query for every ID value...
|
|
|
|
|
bhiller wrote: run the query for every ID value...
What with a cursor or a while loop, what happens if he has 100k IDs. This is a bad solution, using the running total tools will give a much better result.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm currently maintaining a Visual Basic project that makes use of tnsping. In order to do a tnsping, it uses a ProcessStartInfo instance to make the call as if it were using the command prompt(DOS). I was wondering if a library exists (Oracle.DataAccess maybe?) or if there's something built into the .NET libraries that will allow me to do the tnsping without the ProcessStartInfo. Could anyone point me in the right direction?
Thanks in advance for all of your answers.
|
|
|
|
|
SELECT DISTINCT ISNULL(CONVERT(VARCHAR,DR_TimeIn,103),'NOT CHECKED')AS DATE,
DR_EmployeeID AS EMPID ,
EM_FirstName +' '+ isnull(EM_MiddleName,' ' )+' '+isnull(EM_LastName,' ') AS EMPNAME,
FROM SG_Daily_Register
to accept the null values of lastname and firstname...I used the 'isnull' which is not correct..Pls correct the procedure
|
|
|
|
|
|
Try COALESCE instead of ISNULL
modified 19-Nov-18 21:01pm.
|
|
|
|
|
SELECT DISTINCT DECODE(DR_TimeIn, NULL,'NOT CHECKED',DR_TimeIn) as Date,
.............;
If you are using Oracle DECODE if the best.
OR for other Database Server:
SELECT IIf(TimeIn is null,'NOT CHECKED',TimeIn) AS [Date],
........;
|
|
|
|
|
It looks like you just forgot to wrap EM_FirstName in an isnull. Otherwise I don't see why that wouldn't work with MSSQL.
|
|
|
|
|
CONVERT(VARCHAR(5), (DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600))
+ '':'' +
CONVERT(VARCHAR(5), (SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60))
+ '':'' +
CONVERT(VARCHAR(5), (DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60))
When I used this.I got output as 0: -13: -16(negative values)
But I will have to get output as 0:13:16
how to remove negative sign from datetime in sql?
|
|
|
|
|
I may have done you a disservice, seems you have changed to datetime, well done.
Not sure if it will work with datetime but you might try ABS()
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
you've still not read the forum guidlines, you've still not learnt to use descriptive subject lines, you've still not learned to keep it in 1 thread.
You'll learn nothing from this response so i'll just paste the correction. Im not going to bother testing it, so check the syntax and try to understand it.
CONVERT(VARCHAR(5), ABS((DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60)))
|
|
|
|
|
Maybe if you swap both datetime arguments in DATEDIFF, your problem is gone?
|
|
|
|
|
It looks like you're trying to get the hour:minute:second difference between two datetime values. The simplest way to do that is subtract them, then convert that value to char and apply the appropriate formatting.
declare @t1 datetime, @t2 datetime
select @t1 = GETDATE()
--Select @t2 = @t1 + 1:02:03 (hh:mm:ss)
select @t2 = DATEADD(hour, 1, dateadd(minute, 2, dateadd(second, 3, @t1)))
select convert(varchar, @t2-@t1, 108)
You can find other formatting options here[^]
|
|
|
|
|
0:-13:-16
Please help me to remove the negative sign.I have to get output as
0:13:16
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
