Clean up a table & save to another table

I'm trying to filter and update the table AllAgreements (below) by
moving parts of each row into appropriate tables and deleting redundant
rows. This table is just a scratch table used while importing old data
into a new DB.
The situation: A company supplies credits to its customers (other
companies [construction] called Developers) that the customers
(Developers) can use to reduce any bills sent to them by the company.
Each time the credits are supplied they are referred to as a Credit
Agreement. The developers can transfer those credits, or parts of those
credits, to other developers. When a transfer is made they are creating
a new agreement. The Agreement identifier, in correspondence, uses a
user constructed identifier (ID #). The company keeps the original
credit agreement identifier & just adds a sequential letter suffix
representing each iteration of a new agreement off the original (because
the original agreement has one cut-off date that is enforced for all
subsequent agreements that use credits from the original credit agreement).
E.g.:
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
10,'TIF1 MSSH 001A','HARRY',10000.00
11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
12,'TIF1 MSSH 001B','JOE',10000.00
I need to generate a new row in the table Transactions (below) for both
the debit on the original credit agreement and the transfer/credit for
the new credit agreement.
/* BEGIN DDL */
set nocount on
CREATE TABLE AllAgreements ( -- scratch table - no PK
ca_id INTEGER NOT NULL UNIQUE,
"ID #" VARCHAR(20) NOT NULL,
Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
issued_date DATETIME NOT NULL,
amt decimal(9,2) NOT NULL
)
CREATE TABLE Transactions (
ca_id INTEGER NOT NULL REFERENCES AllAgreements,
developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
transaction_type INTEGER NOT NULL
CHECK (transaction_type BETWEEN 1 AND 5),
applied_date DATETIME NOT NULL,
amount DECIMAL(9,2) NOT NULL,
CONSTRAINT PK_Transactions
PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
)
INSERT INTO AllAgreements
VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
INSERT INTO AllAgreements
VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
INSERT INTO AllAgreements
VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
INSERT INTO AllAgreements
VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
INSERT INTO AllAgreements
VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
INSERT INTO AllAgreements
VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
INSERT INTO AllAgreements
VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
INSERT INTO AllAgreements
VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
INSERT INTO AllAgreements
VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
INSERT INTO AllAgreements
VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
INSERT INTO AllAgreements
VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
INSERT INTO AllAgreements
VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
/* desired results in table AllAgreements (I'll remove the Amt column
after the clean up & the values are in the Transactions table. Didn't
show the date in order to display row on one line).
ca_id ID # Developer amt
1 FIF2 GRNBR 001 LUCY $55,000.00
3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
6 TIF1 ACSPA 001 WILLY $30,000.00
7 TIF1 ACSPA 002 WILLY $45,000.00
8 TIF1 MSSH 001 FRED $25,000.00
10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
*/
/* desired results in table Transactions:
(1 = Original credit agreement; 2 = transfer of CR agreement)
E.g.: below the 1st 2 LUCY lines show the original credit
(trans_type=1) and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001"
caused by the transfer to JUDY (line 3). The JUDY line's ca_id, 3,
points to the new credit agreement "FIF2 GRNBR 001A" row in the
AllAgreements table.
ca_id developer transaction_type applied_date amount
-- -- -- -- --
1 LUCY 1 20050115 55000.00
1 LUCY 2 20050122 -20000.00
3 JUDY 2 20050122 20000.00
4 SUSIE 2 20050122 10000.00
3 JUDY 2 20050122 -10000.00
6 WILLY 1 20050211 30000.00
7 WILLY 1 20050211 45000.00
8 FRED 1 20050212 25000.00
8 FRED 2 20050212 -10000.00
10 HARRY 2 20050212 10000.00
10 HARRY 2 20050225 -100000.00
12 JOE 2 20050225 10000.00
*/
DROP TABLE AllAgreements, Transactions
/* END */
QUESTION: What append command will move the amounts to the Transactions
table w/ the correct ca_id value.
Thanks for your help.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)Hi
See inline:
"MGFoster" <mgf00@.earthlink.net> wrote in message
news:8waoe.562$HM.259@.newsread1.news.pas.earthlink.net...
> I'm trying to filter and update the table AllAgreements (below) by moving
> parts of each row into appropriate tables and deleting redundant rows.
> This table is just a scratch table used while importing old data into a
> new DB.
> The situation: A company supplies credits to its customers (other
> companies [construction] called Developers) that the customers
> (Developers) can use to reduce any bills sent to them by the company. Each
> time the credits are supplied they are referred to as a Credit Agreement.
> The developers can transfer those credits, or parts of those credits, to
> other developers. When a transfer is made they are creating a new
> agreement. The Agreement identifier, in correspondence, uses a user
> constructed identifier (ID #). The company keeps the original credit
> agreement identifier & just adds a sequential letter suffix representing
> each iteration of a new agreement off the original (because the original
> agreement has one cut-off date that is enforced for all subsequent
> agreements that use credits from the original credit agreement).
> E.g.:
> 1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
> 2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
> 3,'FIF2 GRNBR 001A','JUDY',20000.00
> 5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
> 4,'FIF2 GRNBR 001B','SUSIE',10000.00
> 6,'TIF1 ACSPA 001','WILLY',30000.00 <- original agreement
> 7,'TIF1 ACSPA 002','WILLY',45000.00 <- original agreement
> 8,'TIF1 MSSH 001','FRED',25000.00 <- original agreement
> 9,'TIF1 MSSH 001A','FRED',-10000.00 <- Fred transferred $10K to Harry
> 10,'TIF1 MSSH 001A','HARRY',10000.00
> 11,'TIF1 MSSH 001B','HARRY',-100000.00 <- Harry transferred $10K to Joe
> 12,'TIF1 MSSH 001B','JOE',10000.00
> I need to generate a new row in the table Transactions (below) for both
> the debit on the original credit agreement and the transfer/credit for the
> new credit agreement.
> /* BEGIN DDL */
> set nocount on
> CREATE TABLE AllAgreements ( -- scratch table - no PK
> ca_id INTEGER NOT NULL UNIQUE,
> "ID #" VARCHAR(20) NOT NULL,
> Developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> issued_date DATETIME NOT NULL,
> amt decimal(9,2) NOT NULL
> )
> CREATE TABLE Transactions (
> ca_id INTEGER NOT NULL REFERENCES AllAgreements,
> developer VARCHAR(5) NOT NULL , -- REFERENCES Developers,
> transaction_type INTEGER NOT NULL
> CHECK (transaction_type BETWEEN 1 AND 5),
> applied_date DATETIME NOT NULL,
> amount DECIMAL(9,2) NOT NULL,
> CONSTRAINT PK_Transactions
> PRIMARY KEY (ca_id, developer, transaction_type, applied_date)
> )
> INSERT INTO AllAgreements
> VALUES (1,'FIF2 GRNBR 001','LUCY','20050115',55000.00)
> INSERT INTO AllAgreements
> VALUES (2,'FIF2 GRNBR 001A','LUCY','20050122',-20000.00)
> INSERT INTO AllAgreements
> VALUES (3,'FIF2 GRNBR 001A','JUDY','20050122',20000.00)
> INSERT INTO AllAgreements
> VALUES (4,'FIF2 GRNBR 001B','SUSIE','20050122',10000.00)
> INSERT INTO AllAgreements
> VALUES (5,'FIF2 GRNBR 001B','JUDY','20050122',-10000.00)
> INSERT INTO AllAgreements
> VALUES (6,'TIF1 ACSPA 001','WILLY','20050211',30000.00)
> INSERT INTO AllAgreements
> VALUES (7,'TIF1 ACSPA 002','WILLY','20050211',45000.00)
> INSERT INTO AllAgreements
> VALUES (8,'TIF1 MSSH 001','FRED','20050212',25000.00)
> INSERT INTO AllAgreements
> VALUES (9,'TIF1 MSSH 001A','FRED','20050212',-10000.00)
> INSERT INTO AllAgreements
> VALUES (10,'TIF1 MSSH 001A','HARRY','20050212',10000.00)
> INSERT INTO AllAgreements
> VALUES (11,'TIF1 MSSH 01B','HARRY','20050225',-100000.00)
> INSERT INTO AllAgreements
> VALUES (12,'TIF1 MSSH 001B','JOE','20050225',10000.00)
> /* desired results in table AllAgreements (I'll remove the Amt column
> after the clean up & the values are in the Transactions table. Didn't
> show the date in order to display row on one line).
> ca_id ID # Developer amt
> 1 FIF2 GRNBR 001 LUCY $55,000.00
> 3 FIF2 GRNBR 001A JUDY $20,000.00 <- Lucy transferred to Judy
> 4 FIF2 GRNBR 001B SUSIE $10,000.00 <- Judy transferred to Susie
> 6 TIF1 ACSPA 001 WILLY $30,000.00
> 7 TIF1 ACSPA 002 WILLY $45,000.00
> 8 TIF1 MSSH 001 FRED $25,000.00
> 10 TIF1 MSSH 001A HARRY $10,000.00 <- Fred transferred to Harry
> 12 TIF1 MSSH 001B JOE $10,000.00 <- Harry transferred to Joe
> */
This seems to be
SELECT A.ca_id,A.[ID #],A.Developer,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.Amt FROM AllAgreements A
WHERE A.Amt > 0
order by A.ca_id

> /* desired results in table Transactions:
> (1 = Original credit agreement; 2 = transfer of CR agreement)
> E.g.: below the 1st 2 LUCY lines show the original credit (trans_type=1)
> and the debit (trans_type=2) on the ID # "FIF2 GRNBR 001" caused by the
> transfer to JUDY (line 3). The JUDY line's ca_id, 3, points to the new
> credit agreement "FIF2 GRNBR 001A" row in the AllAgreements table.
> ca_id developer transaction_type applied_date amount
> -- -- -- -- --
> 1 LUCY 1 20050115 55000.00
> 1 LUCY 2 20050122 -20000.00
> 3 JUDY 2 20050122 20000.00
> 4 SUSIE 2 20050122 10000.00
> 3 JUDY 2 20050122 -10000.00
> 6 WILLY 1 20050211 30000.00
> 7 WILLY 1 20050211 45000.00
> 8 FRED 1 20050212 25000.00
> 8 FRED 2 20050212 -10000.00
> 10 HARRY 2 20050212 10000.00
> 10 HARRY 2 20050225 -100000.00
> 12 JOE 2 20050225 10000.00
> */
>
I can't see the login in the ca_id column as they do not relate to the
transaction concerned! But this is almost what you wanted:
SELECT A.ca_id,--A.[ID #],
B.Developer,
2 Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
B.AMT AS AMT
FROM AllAgreements A
JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
UNION ALL
SELECT A.ca_id,--A.[ID #],
A.Developer AS Developer,
CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
CONVERT(char(10),A.issued_date,112) As Applied_date,
A.AMT AS AMT
FROM AllAgreements A
LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
AND A.[ID #] = B.[ID #]
AND A.Developer <> B.Developer
AND A.issued_date = B.issued_date
AND A.AMT = -1 * B.AMT
WHERE A.AMT > 0
ORDER BY A.CA_ID, AMT

> DROP TABLE AllAgreements, Transactions
> /* END */
>
> QUESTION: What append command will move the amounts to the Transactions
> table w/ the correct ca_id value.
>
You will have to decide what the criteria is for the ca_id.
John

> Thanks for your help.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||John Bell wrote:
> I can't see the login [do you mean logic?] in the ca_id column as they do
not relate to the
> transaction concerned! But this is almost what you wanted:
> SELECT A.ca_id,--A.[ID #],
> B.Developer,
> 2 Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> B.AMT AS AMT
> FROM AllAgreements A
> JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> UNION ALL
> SELECT A.ca_id,--A.[ID #],
> A.Developer AS Developer,
> CASE WHEN B.ca_id IS NULL THEN 1 ELSE 2 END AS Transaction_Type,
> CONVERT(char(10),A.issued_date,112) As Applied_date,
> A.AMT AS AMT
> FROM AllAgreements A
> LEFT JOIN AllAgreements B ON A.ca_id <> B.ca_id
> AND A.[ID #] = B.[ID #]
> AND A.Developer <> B.Developer
> AND A.issued_date = B.issued_date
> AND A.AMT = -1 * B.AMT
> WHERE A.AMT > 0
> ORDER BY A.CA_ID, AMT
>
> You will have to decide what the criteria is for the ca_id.
>
Ah, but that's just what I need the query to do - figure out which ca_id
goes w/ each transfer's credit transaction.
Think of it this way: Lucy transfers $20K from agreement FIF2 GRNBR 001
(ca_id 1) to Judy, which makes a new agreement FIF2 GRNBR 001A (ca_id 2)
for Judy. I need to show in the Transactions table a debit of $20K from
ca_id 1 and a credit to ca_id 2 (easy to do) of $20K.
The following DML command will get the credits:
INSERT INTO Transactions (ca_id, developer, transaction_type,
applied_date, amount)
SELECT ca_id, developer, 2 As trans_type, issued_date, amt
FROM AllAgreements
WHERE amt > 0
It's the DML command that puts the debits, w/ the proper ca_id, that's
the problem.
Ex. 1:
ca_id, "ID #", developer, amt
1,'FIF2 GRNBR 001','LUCY',55000.00 <- original agreement
2,'FIF2 GRNBR 001A','LUCY',-20000.00 <- Lucy transferred $20K to Judy
The 2nd row (ca_id 2) needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
1 Lucy 2 -20000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001.'
Ex. 2:
3,'FIF2 GRNBR 001A','JUDY',20000.00
5,'FIF2 GRNBR 001B','JUDY',-10000.00 <- Judy transferred $10K to Susie
4,'FIF2 GRNBR 001B','SUSIE',10000.00
The above row needs to have an entry in Transactions of:
ca_id developer, trans_type, amount
--
3 Judy 2 -10000.00
Since it is a deduction from agreement 'FIF2 GRNBR 001A.'
IOW, the credits ($) are deducted from the prior agreement:
001A deducts from 001
001B deducts from 001A
I believe I need to add a character "@." to the 001 so the query can
"see" the previous agreement as
Right([current ID #],1) > Right([other ID #],1)
This sorta works:
SELECT C.ca_id, c.[ID #], d.developer, d.transaction_type,
d.issued_date, d.amt
FROM AllAgreements As C,
(SELECT ca_id,[ID #], Developer, 2 AS Transaction_Type,
issued_date, amt
FROM AllAgreements
WHERE amt<0 ) As D
WHERE LEFT(C.[id #],LEN(c.[id #])-1)=LEFT(d.[id #],LEN(d.[id #])-1)
AND ASC(RIGHT(C.[ID #],1)) = ASC(RIGHT(D.[ID #],1))-1
ORDER BY 1, 2
Data from above query (removed the amt & date cols so could display on
one line):
ca_id ID # developer amt
1 FIF2 GRNBR 001@. LUCY ($20,000.00) <- correct
2 FIF2 GRNBR 001A JUDY ($10,000.00) <- incorrect
3 FIF2 GRNBR 001A JUDY ($10,000.00) <- correct
8 TIF1 MSSH 001@. FRED ($10,000.00) <- correct
9 TIF1 MSSH 001A HARRY ($10,000.00) <- incorrect
10 TIF1 MSSH 001A HARRY ($10,000.00) <- correct
Any refinements possible?
Rgds,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Choosing field from same row based on an aggregate.

I have a table with two columns. Let's call them Value and Hour. Looks like this:

Value Hour

4 9:00

3 11:00

6 2:00

2 12:00

I want to be able to make a total line and give the Max(Value) and the time it happened. What would be the function to get the Hour value based on the Max(Value). Just for example, for this one it would be Max(Value) = 6 and it's Hour would be 2:00.

Hi,

what about getting this right back from the database system with a query ?

SELECT Hour,MAX(Value)
From SomeTable
Group by Hour

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I'm actually getting my data from a Custom Data Processing extension. I would need a way to get it with a function straight out of the resultant dataset.|||

Can't you make a calculated field with the expression = Max(Value)?

That can be put in the footer of the table (which will display '6' in this example). To get the hour belonging to the value with an expression ...I'm not so sure how to do that ... maybe with a switch expression, based on the textbox where you show the MAX(Value)?

checksum_agg and row size error. Require explanation.

Hi,

I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?

-- Works OK
select o.id
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name

-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x

Regards

LiamUse "OPTION (ROBUST PLAN)":

select o.name
,checksum_agg(binary_checksum(*m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
OPTION (ROBUST PLAN)

This forces SQL Server to use a plan that works for the maximum
potential row size.

Razvan

Checking for row existence with secondary key

Hi -
I'm no SQL wizard (obviously). I have a table (conceivably very
large (500k+rows)) with a non-unique secondary index. I need an
efficient query to check for row existence using that secondary index.
Any ideas will be appreciated...
Thanks,
BryanIF EXISTS (SELECT * FROM yourTable AS a WHERE a.Col = YourCondition)
-- do your stuff here
Andrew J. Kelly SQL MVP
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan|||Bryan,
DDL would help but try:
SELECT ID
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID)
HTH
Jerry
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan

Checking for Row Existence

Can someone show me some C# code for detecting if a SQL row exists or not? This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step. In my code I'm either going to INSERT or UPDATE a record. I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response. Apparently ExecuteNonQuery does not work with SELECT. I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.

So...can anyone share the typical code they use to identify if a row exists or not within a database. I guess I was execting there to be some method available to do this sort of thing.

There are two methods i use

Method A)

Which uses the Execute Scalar to return the results of a SQL COUNT

i.e.

 cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
 Int32 count = (Int32) cmd.ExecuteScalar();

Here is a nice example i found for you

http://www.java2s.com/Code/CSharp/Database-ADO.net/GetrowcountbyExecuteScalar.htm

Method B)

Using a datareader and has.rows

Add ; to make it c# as you see fit

Dim objTransport As SqlDataReader
objTransport = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

If objTransport.HasRows Then
Return ("True")
Else
Return ("False")
End If
myConnection.Close()

Method A is more efficient as your only passing around a very small number between the webserver and database.

Hope that helps

Checking for duplicate row before inserting

I need to know how to check/compareall fields in a row based on Cust Id before inserting a new record. As it is right now, if the Add/Update button is pressed continually, it increments the record number and adds a duplicate record. And you know you'll never stop people from double clicking a button that only needs to be clicked once.

I thought about just disabling the Update button after the insert occurs, then re-enable it if they go back and edit a field, but that's not a solid solution.I'd do it in your stored procedure.

You've got something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
GO

I'd change it to something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
IF EXISTS
 (SELECT CustID FROM CustTable
 WHERE SomeField = @.OneVal AND OtherField = @.OtherVal)
 BEGIN
 RETURN
 END
ELSE
 BEGIN
 INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
 END
GO

That's pretty simple, obviously...You'll likely want to return something if a match is found, or even if the new record gets inserted...But it shows you the basics, I hope.

Regards,

Xander|||That looks like what I'm looking for. Since the add buttons click event runs 5 functions to insert data into other tables; if I call the SP in the first function, that should intercept the insertion process all together and break right?

InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

I suppose that I could code a boolean to catch if the SP returns a duplicate using True/False, then use it to display a message in my errmsg label; notifying the user that they have attempted to store a duplicate record.

Thanks for the help, any other info would be greatly appreciated.
PD|||InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

No, the fact that a record wasn't inserted will not, in and of itself prevent the other functions from firing.

You've got the idea though...If you pass a boolean value back, you can use it to decide whether to fire the other functions or not.

You could pass an output parameter from the stored procedure, for instance, or return a scalar value that lets you know what the stored procedure did, and wrap your four subsequent functions in an IF block, (perhaps even inside the first function on successful insertion?) and that should give you what you want.|||I got it to work, but it does prevent the updating of the fields that are not part of the SP in one of my functions (Strange).

I am going to check it out. I thought about appending the table to store all the field data and re-code the SP to check all the fields, fire it first, and then let it yield to the other functions if no duplicate exists.

Thanks.|||Well, it has turned into a proverbial nightmare. If I yield to the other insert functions from my SP, then duplicate records can be created in those tables. So I guess I am going to have to write SP's for each of those functions to prevent duplicates there as well.

I inherited this app a couple of months ago from an outside firm, and what a mess it has been.

Checking existence with insert... select OPENXML

Hi,
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:

> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>

checking a CSV file

Hi,

I'm trying to import some data from a CSV file into a table in SQL server. At around row 40.000 SQL server gives an error. It says: Column delimiter not found. I think that there went something wrong creating the CSV file. I thought of just opening the CSV file en checking it by hand but it is to big to open. Does anyone know if there i a way to check a CSV file with a program or something?

JoachimYou can try using MS Access. Create the same table etc. Try importing your csv again and it should discard any invalids rows. Or try and achieve that with a DTS job

Checkbox in reports

I have a report on which i want to add a checkbox to the extreme left of
every row and allow the user the ability to select it, hit a button named
"Export" and move the contents of only that row to MS Excel.
Does Reporting services 2000 support this? Any suggestions on how this can
be done?
Thanks...uh; i'd start with 'not trying to use excel for reporting'
-Aaron
Gaurav wrote:
> I have a report on which i want to add a checkbox to the extreme left of
> every row and allow the user the ability to select it, hit a button named
> "Export" and move the contents of only that row to MS Excel.
> Does Reporting services 2000 support this? Any suggestions on how this can
> be done?
> Thanks...

Check to see if a row exists in another table before insert

I have a stored procedure that selects invoices based on the date range

delete from BillingCurrent

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

delete from Billing30

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3

Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.

You can use something along the lines of the code listed below.

Chris

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

END

ELSE

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

|||

Ok I understand that and it's very helpful but,

for

IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is found

I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables

END

|||

There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.

Chris

IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Billing30 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)

BEGIN

--Insert the SQL that you want to execute if a Table2 record is not found

END

IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)

etc....

|||

ok so I can nest all of the if not exists and then after those just

if exists

end

to make it do nothing if the row already exists?

|||

In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.

It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.

Chris

|||

Ok, Heres my SP

This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

IF NOT EXISTS (SELECT CustomerID FROM Billing90)

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

|||

Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...

Chris

DECLARE @.Billing120Exists BIT

DECLARE @.Billing90Exists BIT

DECLARE @.Billing60Exists BIT

DECLARE @.Billing30Exists BIT

SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END

SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END

SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END

SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END

--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.

--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.

|||

Actually I ended up doing it this way,

delete from Billing120

insert into Billing120(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120

delete from Billing90

BEGIN

insert into Billing90(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90

END

delete from Billing60

BEGIN

insert into Billing60(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60

End

delete from Billing30

BEGIN

insert into Billing30(CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30

END

delete from BillingCurrent

BEGIN

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip

FROM Invoices INNER JOIN

Customers A ON Invoices.CustomerID = A.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)

And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)

And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)

And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)

select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent

END

RETURN

Thanks for the Help!