Showing posts with label append. Show all posts
Showing posts with label append. Show all posts

Tuesday, March 27, 2012

Converting column values

I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.

Can anyone suggest an approach.I'd use something like:UPDATE myTable
SET bogusColumn = - Abs(bogusColumn)-PatP

Thursday, March 22, 2012

Converting .dbf to sql server table...batch file? command line?

My client will be receiving a .dbf file which needs to be uploaded
into a sql server database table (as an append) every week. They are
NOT computer savvy and I would like to automate this process rather
than go into enterprise manager and run data transformation.

Is there any way to write a batch file or a set of command lines which
will do this?

Thanks.

Monicamonica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monica
DTS package would probably be the easiest solution. Create the package
as an import from the .dbf into the approriate table and then run it
with each new .dbf received. If running DTS packages is a bit
'complex' for your users you could wrap it up in an applicaion/wizard
(eg in VB) and execute it that way.|||monica@.datashark.net (Monica J. Braverman) wrote:
>My client will be receiving a .dbf file which needs to be uploaded
>into a sql server database table (as an append) every week. They are
>NOT computer savvy and I would like to automate this process rather
>than go into enterprise manager and run data transformation.
>Is there any way to write a batch file or a set of command lines which
>will do this?

You might create a DTS package on the server, then create a batch file
to access it that calls the DTSRUN.exe program. I have a batch file
with this as the command line:
dtsrun /e /n"dtspackagename" /sSQLServerName and it works very well.
hth,
Myron|||Hi,
you can automate your process using a command prompt utility named
"bcp"; if you try to execute this command you get the syntax:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide
character type]
[-N keep non-text native] [-V file format version] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row
terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional
enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

Another way is to use the T-SQL command "BULK INSERT".
You can find explanations on your MS-SQL documentation.

Bye.

monica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monicasqlsql

Monday, March 19, 2012

Convert to SQL Function! Help!

Can u help me transform this code into sql function?

/* Append modulus 11 check digit to supplied string of digits. */
function GenMOD11( $base_val )
{
$result = "";
$weight = array( 2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7 );

/* For convenience, reverse the string and work left to right. */
$reversed_base_val = strrev( $base_val );
for ( $i = 0, $sum = 0; $i < strlen( $reversed_base_val ); $i++ )
{
/* Calculate product and accumulate. */
$sum += substr( $reversed_base_val, $i, 1 ) * $weight[ $i ];
}

/* Determine check digit, and concatenate to base value. */
$remainder = $sum % 11;
switch ( $remainder )
{
case 0:
$result = $base_val . 0;
break;
case 1:
$result = "n/a";
break;
default:
$check_digit = 11 - $remainder;
$result = $base_val . $check_digit;
break;
}Owh..i forget.. I'm using SQL Server 2000|||moving thread to SQL Server forum|||Can you explain to us what the above function actually does (step by step preferred) and then we can see if we can help! :)|||The reversing confuses me slightly. Is the last digit always multiplied by 2?|||May I contribute:-- ptp 20070806 SQL Server mod-11 function
-- See http://www.dbforums.com/showthread.php?t=1621130 for discussion
-- Note: Mod-11 was once a well known checkdigit algorithm. It was implemented in hardware
-- on the 129 keypunch, and it still used in ISBN and banking applications in 2007

CREATE FUNCTION dbo.fMod11(@.pcFundus VARCHAR(20))
RETURNS VARCHAR(21) AS

BEGIN
DECLARE @.iAccumulator BIGINT -- Accumulator for weighted sum
, @.iDigits INT -- Digit place value
, @.iNoise INT -- Noise characters ignored
, @.cChar CHAR(1) -- Current working character
, @.cResult VARCHAR(21) -- Result value to return
, @.cWork VARCHAR(21) -- Scratch buffer

SET @.cResult = @.pcFundus -- Assume we return what we got
SET @.cWork = Reverse(@.pcFundus) -- Reverse to make string handling simpler
SET @.iAccumulator = 0 -- Accumulator starts at zero
SET @.iDigits = 1 -- 1 is offest for the check digit

WHILE 0 < Len(@.cWork) -- Loop to process all characters
BEGIN
SET @.cChar = Left(@.cWork, 1) -- Current char is leftmost
SET @.cWork = SubString(@.cWork, 2, 21) -- then peel it off the buffer

IF 0 < CharIndex(@.cChar, '0123456789') -- Digit character
BEGIN
SET @.iDigits = 1 + @.iDigits -- bump digit count
SET @.iAccumulator = @.iDigits * (Ascii(@.cChar) - 48) + @.iAccumulator
END
ELSE IF 0 < CharIndex(@.cChar, ' -') -- Defined "noise" character?
SET @.iNoise = 1 + @.iNoise
ELSE -- Garbage, bail out!
BEGIN
SET @.cResult = NULL
SET @.cWork = ''
END
END

RETURN @.cResult + SubString('0123456789XX', 12 - @.iAccumulator % 11, 1)
END
GO
SELECT d1 + d0, dbo.fMod11(d1 + d0) -- Prove that we've got it correct
FROM (
SELECT 0 AS d0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (
SELECT 0 AS d1 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS z1
ORDER BY d1 + d0
GO
DROP FUNCTION dbo.fMod11 -- Tidy up after we've played in the sandbox-PatP|||Pat, I don't believe that your @.iDigits does the same thing as massspectrometry's weight array as the string gets longer than 6 characters.
You seem to multiply the 7th number by 8, and mass's function multiplies it by 2.|||I'll conceed that my Transact-SQL function and the PHP function aren't identical.

My algorithm implements Mod-11 as it is used for ISBN, banking, etc. It specifically allows for "noise characters" that are permissible in those uses, and it correctly computes the checksum for a fundus value with a value of zero or a remainder of ten (using an X for the checksum character).

-PatP|||Can you explain to us what the above function actually does (step by step preferred) and then we can see if we can help! :)

Hai..thank you for offering.. I'm trying to make a sql function that will perform similarly like this url http://www.eclectica.ca/howto/modulus-11-self-check.php

I have 7 digits data (SERIALNO), and i want to get the modulus 11 from this 7 digits data..

For example..
SERIALNO || MODULO 11
1000001 || 10000017
1000002 || 10000025
1000003 || 10000033
1000004 || 10000041

Can it be done? How?|||Here is the code with the 5250 bug faithfully re-implemented:-- ptp 20070806 SQL Server implemented of IBM 5250 mod-11 function
-- See http://www.dbforums.com/showthread.php?t=1621130 for discussion
-- Note: Mod-11 was once a well known checkdigit algorithm. A derivative of mod-11
-- was implemented in hardware on the 5250 terminal

CREATE FUNCTION dbo.fMod11(@.pcFundus VARCHAR(20))
RETURNS VARCHAR(21) AS

BEGIN
DECLARE @.iAccumulator BIGINT -- Accumulator for weighted sum
, @.iDigits INT -- Digit place value
, @.iNoise INT -- Noise characters ignored
, @.cChar CHAR(1) -- Current working character
, @.cResult VARCHAR(21) -- Result value to return
, @.cWork VARCHAR(21) -- Scratch buffer

SET @.cResult = @.pcFundus -- Assume we return what we got
SET @.cWork = Reverse(@.pcFundus) -- Reverse to make string handling simpler
SET @.iAccumulator = 0 -- Accumulator starts at zero
SET @.iDigits = 1 -- 1 is offest for the check digit

WHILE 0 < Len(@.cWork) -- Loop to process all characters
BEGIN
SET @.cChar = Left(@.cWork, 1) -- Current char is leftmost
SET @.cWork = SubString(@.cWork, 2, 21) -- then peel it off the buffer

IF 0 < CharIndex(@.cChar, '0123456789') -- Digit character
BEGIN
SET @.iDigits = -- Next 5250 digit weight
CASE
WHEN 7 = @.iDigits THEN 2
ELSE 1 + @.iDigits
END
SET @.iAccumulator = @.iDigits * (Ascii(@.cChar) - 48) + @.iAccumulator
END
ELSE IF 0 < CharIndex(@.cChar, ' -') -- Defined "noise" character?
SET @.iNoise = 1 + @.iNoise
ELSE -- Garbage, bail out!
BEGIN
SET @.cResult = NULL
SET @.cWork = ''
END
END

RETURN @.cResult + SubString('0123456789XX', 12 - @.iAccumulator % 11, 1)
END
GO
SELECT dbo.fMod11('100000' + d)
FROM (SELECT '0' AS d UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5') AS z

SELECT d1 + d0, dbo.fMod11(d1 + d0) -- Prove that we've got it correct
FROM (
SELECT 0 AS d0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (
SELECT 0 AS d1 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS z1
ORDER BY d1 + d0
GO
DROP FUNCTION dbo.fMod11 -- Tidy up after we've played in the sandbox-PatP|||Erm.. i feel really shy of asking this.. how to call the function? (embarrassed)|||See the sample code at the end of the snippet that I posted. Your test is in there.

-PatP|||Thank you pat phelan.. in the code here :

SELECT dbo.fMod11('100000' + d)
FROM (SELECT '0' AS d UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5') AS z

For example if i have 100,000 data.. starting from 1000001 until 1100000.. How am i to code it? Is it one by one?|||FYI - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87357|||None of the code or links worked for my situation. I'm posting what I hope is a generic mod 11 user defined function. I'm sure there are improvements that could be made to it, but it works for my need (10 numeric digits, last digit being a mod11 check digit).

/*
Checks if a number passes the Mod11 checksum algorithm.

Input:
@.NumberToCheck = the number to check for validity (last digit is the check digit)

Returns:
1 = the number provided passes the Mod11 checksum algorithm
0 = the number provided does not pass the Mod11 checksum algorithm
*/

CREATE Function dbo.ufn_Mod11(@.NumberToCheck as varchar(10))
Returns bit As
Begin
/*
-- FOR TESTING --------
Declare @.NumberToCheck as varchar(10)
Set @.NumberToCheck = '7830834260'
Set @.NumberToCheck = '7806812519'
-- FOR TESTING --------
*/
Declare @.CheckDigit int
Declare @.Counter int
Declare @.IsValid bit
Declare @.Product int
Declare @.Sum int

-- Assume it is not valid.
Set @.IsValid = 0

-- Number must not be null, must be numeric, must be ten digits.
If @.NumberToCheck Is Not Null And IsNumeric(@.NumberToCheck) = 1 And Len(@.NumberToCheck) = 10
Begin
Set @.Counter = 1
Set @.Sum = 0

-- Reverse the number being checked.
Set @.NumberToCheck = Reverse(@.NumberToCheck)

-- Iterate through all digits except the last digit.
While @.Counter <= Len(@.NumberToCheck)
Begin
-- Multiply the digit by its position, starting with the second one.
If @.Counter > 1
Begin
Set @.Product = SubString(@.NumberToCheck, @.Counter, 1)

Set @.Product = @.Product * @.Counter

-- Sum the current product.
Set @.Sum = @.Sum + @.Product
End

Set @.Counter = @.Counter + 1
End

Set @.CheckDigit = @.Sum % 11

-- If the check digit is ten, just set it to zero.
If @.CheckDigit = 10
Begin
Set @.CheckDigit = 0
End

-- Compare the calculated check digit to the original last digit,
-- which is now the first since it was reversed.
If @.CheckDigit = Left(@.NumberToCheck, 1)
Begin
Set @.IsValid = 1
End
End

Return @.IsValid
End

Sunday, February 12, 2012

CONVERT Error Message

Greetings,
I have a "newbie" question in relation to dates. I am trying to append the
year, month, and day to one another in order to create a new date. The SQL
I
am using is below. The error message I am receiving is "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." I have also tried using "CAST", but I receive a similar error
message on that as well. Any idea as to what I am doing wrong?
Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
Convert(varchar(12),year(ebm.Hire_Date + 30))
Thanks in advance!
--
SherwoodSherwood (Sherwood@.discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append
> the year, month, and day to one another in order to create a new date.
> The SQL I am using is below. The error message I am receiving is "The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value." I have also tried using "CAST", but I
> receive a similar error message on that as well. Any idea as to what I
> am doing wrong?
> Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
> Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
> Convert(varchar(12),year(ebm.Hire_Date + 30))
There are two safe date formats in SQL Server (three in SQL 2005). The
most commonly used is YYYYMMDD. When you use delimited formats, it's up
to the settings how the date will be interpreted.
Anyway, I am not really sure what you want to do, but you should have a
look at the dateadd() function, that may simplify your problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was able to resolve it by using the code below. The reason it initially
failed was due to the fact that I was using it in one of the conditions of a
CASE statement. The other condition had a different format and apparently
that executed first. At any rate, the code below seems to be working.
Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
Convert(varchar(15),day(ebm.Hire_Date))
Thanks.
--
Sherwood
"Erland Sommarskog" wrote:

> Sherwood (Sherwood@.discussions.microsoft.com) writes:
> There are two safe date formats in SQL Server (three in SQL 2005). The
> most commonly used is YYYYMMDD. When you use delimited formats, it's up
> to the settings how the date will be interpreted.
> Anyway, I am not really sure what you want to do, but you should have a
> look at the dateadd() function, that may simplify your problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sherwood (Sherwood@.discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it
> initially failed was due to the fact that I was using it in one of the
> conditions of a CASE statement. The other condition had a different
> format and apparently that executed first. At any rate, the code below
> seems to be working.
> Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
> Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
> Convert(varchar(15),day(ebm.Hire_Date))
Hire someone in December, and you will get a nasty surprise.
This does work:
dateadd(MONTH, 1, ebm.Hire_date)
And this is what you should use.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx