Bulk Insert/Update Ideas

I need a fresh set of eyes.

On a daily basis I need to perform a bulk update. Table totals about 50,000 records with approximately 5,000 changing (deletes, edits, and new records) per day. I'd like to push just the updates somehow, but VB is too slow and I haven't found a way in to handle it in DTS. Not much experience w/ DTS.

I'm transfering between two SQL 2000 servers w/ a VB app sitting in the middle.

Any ideas?you can break the incoming file into 3 corresponding to operations (insert, update, delete) using findstr with redirection (>)

then for insert you just do a straight bulk insert, while for update and delete do a bulk insert into a staging table and then delete and update by joining it with the live table.

Bulk insert problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update

Buisnes Managment System

i am desighning a database that looks after all expences of a buisnes. please any ideas would be welcome with regards to the user interface and requirements that you would need inorder for the product to function for your buisnes or needs

Hope to here from you soon

Thank you in advance
Simon Burgess

Dmop123@.hotmail.com
Email and msnThe obvious question is why don't you just use one of the dozens of off-the-shelf accounting packages? You will definitely be reinventing the wheel here.|||all If that is the case then how has the wheel transformed form wooden spocked cart wheels to the qulity of pirelli (whitch would you put on your car).

Within every thing there is always room for inprovement as nothing is perfect, but things can only get beter if constructive critisism is given.

chears|||One person may carve a wagon wheel but it takes a team of experts in many fields to create a Pirelli, plus one more to improve on it.|||thank you.