Inserting bulk data to SQL DB (SQL 2000) [modified] - Database Discussion Boards

pmarfleet21-Dec-07 22:25

21-Dec-07 22:25

You haven't stated what database you are using. However, if you are using SQL Server then you can use the COALESCE function. COALESCE returns the first non-null value in a series of values. It checks these values from l-r, so you can define an order of precedence. See the following example:

SELECT COALESCE([office], [cell], [residence]) AS Phone FROM MyTable

Hope this helps.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Check box = Unchecked

s3rro21-Dec-07 8:00

s3rro

21-Dec-07 8:00

Hi i have an access db where i have a check box for new items on the list,
I want to un-check it after viewing that item through my web application.
How do u set a check box to unchecked?

New? is the name of the column where i have the checkboxes.

i thought its like this;
string sql = "INSERT into ([New?])" + " VALUES ( '" + false +"')";

but this doesnt work

thanks

Re: Check box = Unchecked

pmarfleet21-Dec-07 9:07

pmarfleet

21-Dec-07 9:07

s3rro wrote:
string sql = "INSERT into ([New?])" + " VALUES ( '" + false +"')";

Your query should work if you remove the quotes surrounding the literal value false.

BTW, it's actually a Boolean field. The check boxes are just a presentation feature provided by Access when viewing the contents of the table.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Check box = Unchecked

Dave Kreskowiak21-Dec-07 9:51

Dave Kreskowiak

21-Dec-07 9:51

This query won't update anything. It's an INSERT query, which means it creates new records.

There is also no WHERE clause, so even if you changes the INSERT to UPDATE, it would change ALL of the records in the table, not just the ones that have their [New?] field set to True.

You also don't specify which table your making an update to.

Your column name is also pretty bad. Don't use punctuation marks in table or column names. This will make for some very interesting debugging sessions if you do.

I think your query, assuming you want to change all the records that are tagged "new" to false and your [New?] column is of type "Yes/No", should look something like this:

UPDATE tableName
SET [New?] = False
WHERE [New?] = True

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Check box = Unchecked

GuyThiebaut23-Dec-07 10:44

GuyThiebaut

23-Dec-07 10:44

I don't know which version of Access you are using but in 97 and 2000 I believe true is -1 and false is 0.

So if all else fails with updating the database give these values a go.

Regards

Guy

P.S. Can anyone explain to me why M$ set true to -1 and not 1?

You always pass failure on the way to success.

Deleteing From Table A When Records Not Found in Table B

gunner_uk200021-Dec-07 3:26

gunner_uk2000

21-Dec-07 3:26

I have a table Task which has the primary key as TaskID and a projectID.

I have another Table called Successors which has two fields TaskID and SuccessorID which are both forigen keys from Task.TaskID

What I need to do is create a statement so that All Tasks where proeject ID = X, are deleted if their Task ID is not found in any of the records in the successor table.

Anyone have any ideas?

Re: Deleteing From Table A When Records Not Found in Table B

Michael Potter21-Dec-07 4:24

Michael Potter

21-Dec-07 4:24

This should get you started.

DELETE FROM 
    Task
WHERE
    ProjectID NOT IN (SELECT 
                          ProjectID 
                      FROM
                          Task t
                      INNER JOIN
                          Successor s
                          ON (t.TaskID = s.TaskID))

Inserting bulk data to SQL DB (SQL 2000) [modified]

Daminda21-Dec-07 1:51

Daminda

21-Dec-07 1:51

I am generating bulk invoices using front end and when I try to insert it to the data base it takes hours it’s unacceptable.

This is how I am doing the task
Get relevant Cx accounts to data tables
Generates Invoice header and detail records and store those records on Invoice Header and Detail Data tables

Loop the invoice header data table and Insert recodes using Strode procedure.
Loop the invoice detail data table and Insert recodes using Strode procedure.

To generate for 200000 invoices it takes more than 24 Hrs.

How can I improve the performance?

Please refer the code.

//Inserting the Invoice Header records
foreach(DataRow InvHDR in DS_BillData.Tables["InvoiceHeaderInformation"].Rows)
{

SqlParameter[] para =new SqlParameter[14];
para[0]=new SqlParameter("@GUAccountID",InvHDR["GUAccountID"]);
para[1]=new SqlParameter("@GUInvID",InvHDR["GUInvoiceID"]);
para[2]=new SqlParameter("@InvoiceNo",InvHDR["InvoiceNo"]);
para[3]=new SqlParameter("@InvoiceDate",StartDate);
para[4]=new SqlParameter("@DueDate",DueDate);
para[5]=new SqlParameter("@BatchNo",BatchID);
para[6]=new SqlParameter("@GUStatmentID",InvHDR["GUInvoiceID"]);
para[7]=new SqlParameter("@BatchSNCount",BatchSNCount);
para[8]=new SqlParameter("@PAYMENTRECIVED",InvHDR["PAYMENTRECIVED"]);
para[9]=new SqlParameter("@SubTotal",InvHDR["SubTotal"]);
para[10]=new SqlParameter("@InvoiceAmount",InvHDR["InvoiceAmount"]);
para[11]=new SqlParameter("@VAT",InvHDR["VAT"]);
para[12]=new SqlParameter("@OtherTax",InvHDR["OtherTax"]);
para[13]=new SqlParameter("@StatmentNo",InvHDR["InvoiceNo"]);
//para[13]=new SqlParameter("@WaveOFF","Y");

callSp("BillingInvoiceCreation_forFront",para);

}

Stored Procedure---

Procedure BillingInvoiceCreation_forFront
(
@GUAccountID as nvarchar(30),
@GUInvID as nvarchar(30),
@InvoiceNo as numeric,
@InvoiceDate as datetime,
@DueDate as datetime,
@BatchNo as Decimal,
@GUStatmentID as nvarchar(30),
@StatmentNo as numeric,
@BatchSNCount as decimal,
@PAYMENTRECIVED as decimal(9,2),
@SubTotal as decimal(9,2),
@InvoiceAmount as decimal(9,2),
@VAT as decimal(9,2),
@OtherTax as decimal(9,2)

)
as
BEGIN

Declare @PrintDescription as nvarchar(100)
Declare @GUItemID as nvarchar(30)
Declare @PaymentAmt as decimal(9,2)
Declare @DueAmount as decimal(9,2)
Declare @CreditNoteAmount as decimal(9,2)
Declare @BFAmount as decimal(9,2)

INSERT INTO B_BillingInvoiceHeader(StatementNo, GUStatementID, GUInvID,
CompanyID, LocationID, BranchID, DeptID, InvoiceNo,
InvoiceDate, DueDate, Status, GUAccountID, ManualInvoice,
BatchNo, InvoiceType,BatchSNCount,BillingYear,BillingMonth,SubTotal,InvoiceAmount,VAT,OtherTax)
VALUES(@StatmentNo, @GUStatmentID, @GUInvID,
1, 1, 1, 1, @InvoiceNo,
@InvoiceDate, @DueDate, 'New', @GUAccountID, 'N',
@BatchNo, 'Batch',@BatchSNCount,year(@InvoiceDate),month(@InvoiceDate),@SubTotal,@InvoiceAmount,@VAT,@OtherTax)

SELECT @BFAmount=isnull(SUM(InvoiceAmount - PaidAmount),0)
FROM B_BillingInvoiceHeader
WHERE (GUAccountID = @GUAccountID) AND (PaymentCompleted = N'N') AND BatchNo<@BatchNo

/*_________________ Add Sanka ______________________*/

exec BillingCustomerAdvances @GUInvID

--- Late Invoice Delocate-----------------------------------------------------------------<<(*_*)>>-----

SELECT @PaymentAmt = PaymentReceve From B_CustomerPaymentRec Where GUAccountID=@GUAccountID And BatchNo=@BatchNo

SELECT @CreditNoteAmount=isnull(SUM(Amount) ,0)
FROM B_CreditNote
WHERE (Status='APPROVED') And (Completed ='N') AND (GUAccountID = @GUAccountID)

INSERT INTO B_MonthlyOpenningBalance
(GuAccountID, BatchNo, OppenningBalance, ClosingBalance, CreditNote, AdvanceBalance, UtilizedCredits)
VALUES (@GuAccountID, @BatchNo, @BFAmount, @BFAmount+@InvoiceAmount, @CreditNoteAmount, 0, 0)

INSERT INTO B_MonthlyBF
(CompanyID, LocationID, BranchID, DeptID, BatchNo, BFAmount, GUAccountID)
VALUES (1, 1, 1, 1, @BatchNo, @BFAmount, @GUAccountID)

Update B_CreditNote Set Completed ='Y' WHERE (Status='APPROVED') And (Completed ='N') AND (GUAccountID = @GUAccountID)

END

------

Thanks for the help i am working on DTS update method con any one give me a direction or small sample code kind of a thing?

Regards,
Daminda.

modified on Friday, December 21, 2007 9:30:11 AM

Re: Inserting bulk data to SQL DB (SQL 2000)

pmarfleet21-Dec-07 2:23

pmarfleet

21-Dec-07 2:23

You should examine the execution plans for your queries and use the Profiler tool to identify the bottlenecks in your code.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Inserting bulk data to SQL DB (SQL 2000)

Paddy Boyd21-Dec-07 2:27

Paddy Boyd

21-Dec-07 2:27

Have you considered using something like DTS or SSIS instead. They are set up to bulk load data.

Custom Auto Generated ID

xavier198821-Dec-07 0:58

xavier1988

21-Dec-07 0:58

Hi I wish to create a custom auto generate ID, such as P110, P111 which is generated automatically is there ways to set it in sql server?Thanks alot

Re: Custom Auto Generated ID

pmarfleet21-Dec-07 2:21

pmarfleet

21-Dec-07 2:21

You can set an int column to be an IDENTITY field to generate sequential numbers in that field. If you want your sequence to be alphanumeric, you should create a computed column for that purpose.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Databas connection with java script

rajesh4u20-Dec-07 22:48

rajesh4u

20-Dec-07 22:48

Hi
can any one knows how to establish database connection with java script

thanks in advance

Re: Databas connection with java script

Colin Angus Mackay21-Dec-07 0:12

Colin Angus Mackay

21-Dec-07 0:12

rajesh4u wrote:
can any one knows how to establish database connection with java script

You could use "Astoria" - now known as ADO.NET data services: Astoria website[^]

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Re: Databas connection with java script

NetBot22-Dec-07 18:26

NetBot

22-Dec-07 18:26

You can do it using Ajax only.

If u r using VS 2005, u can use the Ajax control toolkit for this purpose

How to avoid the alphabetic order of the columns returned when selected from a SQL linked server.

kalam20-Dec-07 22:34

kalam

20-Dec-07 22:34

Hi,

I created a SQL Linked server for excel file. I am using that Linked server to Import the data from Excel files.When selecting the reords from this linked server , it returns the data in alphabetical order.
But I want the same order of columns which is in Excel file.

for selecting the records i am using tis Query:
select * from ExcelImport...Test$

where, ExcelImport is Linked server name and Test$ is the sheet in the excel file.

Please help.

kalam.

Re: How to avoid the alphabetic order of the columns returned when selected from a SQL linked server.

Paddy Boyd21-Dec-07 1:03

Paddy Boyd

21-Dec-07 1:03

Can you not provide the named columns in the select statement:

e.g. Select cola, othercolumnname from excelimport

Sql Server Login

cbkulathunge20-Dec-07 20:05

cbkulathunge

20-Dec-07 20:05

hi,
I need to login to remote sql server trough my Application and need to change the server location

Re: Sql Server Login

Paul Conrad22-Dec-07 7:05

Paul Conrad

22-Dec-07 7:05

Is there a problem you are having? Your post is too vague.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Need Query for Searching Name from database.

mithun narayanan20-Dec-07 19:20

mithun narayanan

20-Dec-07 19:20

i need a query for checkin a name whether it exists or not in database,,
am storing firstname nd lastname in seperate columns.

bt if i enter a first name only in textbox i need to display all names(firstname+lastname) ,starting from the entered first name

PLZ HELP...
Waiting 4 reply...

Re: Need Query for Searching Name from database.

^That's Aragon20-Dec-07 19:31

^That's Aragon

20-Dec-07 19:31

Hi Mithun,

Please try this. Hope it will help you.

if exists(select ID from Tablename where nameFirst like '%Your TextBox Value%')<br />
<br />
select nameFirst + SPACE(1) + nameLast As Name from Tablename where nameFirst like '%Your TextBox Value%'

Keep Smiling Smile | :)

Re: Need Query for Searching Name from database.

mithun narayanan20-Dec-07 20:04

mithun narayanan

20-Dec-07 20:04

Thank u Boss its Workin nw... Big Grin | :-D

bt am passing the name as a parameter

where cand_fname like @cand_fname

its nt wrking

bt if give as cand_fname like 'mith'

this works
so how change that

Re: Need Query for Searching Name from database.

mithun narayanan20-Dec-07 20:50

mithun narayanan

20-Dec-07 20:50