Is there anything like DoEvents in a stored proc?

Hello,
I have a stored proc that does a series of things
1) create table if doesn't exist else delete all rows
2) select into this table
3) create another table if doesn't exist else delet all rows
4) select large number of records into this table
5) count records
6) delete records if more than a certain number of records
and I also print status in between each for debugging.
I am wondering if there is a way to have the stored procedure give up
some cpu time and also output the print statements before the entire
stored proc has finished.
Thanks
Randy
It seems that your procedure is written ineficiently...
prints are just data and get returned when you come to them.
Probbaly Waitfor Delay will do the job for you.
Bojidar Alexandrov
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com ...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>
|||I've used PRINT statements for debugging large SPROCS - they do not come out
in the expected spot at all times.
I assume this is related to the way ADO from VISUAL BASIC has similar
issues - the PRINT statements go into the ERROR COLLECTION of the CONNECTION
OJBECT and seem to wait for RECORDSET processing - can't seem to get both.
In QUERY ANALYZER I've gotten around this issue by sometimes setting the
RESULT PANE to RETURN TEXT as opposed to DATA in a GRID - that way you get
one result window for both the RECORDSET data and the PRINT statements.
I've also at times changed all my PRINT statements to be SELECT statements
for debugging. And in reality if some of those "prints" remain in the
production code, it's better to have them as SELECT statements.
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com ...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>
|||> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
I would question whether it makes sense to move data around in this
inefficient sequential manner. Can you create a view rather than copying
rows into another table? Inserting rows then counting and deleting them
seems especially pointless. Why can't you write your query (4) to insert
only the rows you require to start with?
If you need more help, please post DDL, sample data and show your required
result.
http://www.aspfaq.com/5006
David Portas
SQL Server MVP
|||Thanks for the reply.
The reason I can't just select the records I need in the first place is
because I have to make sure that I have complete months worth of data.
So my logic is something like this.
1) Select top 104000 records into temp table
2) Select count if I have exactly 104000 records then I know that most
likely I have a partial months worth of data so now select the oldest
date, do some date math so that I have the date of the first day of the
next month then delete all records that are older than that.
It is actually even more complicated than that because I am building up
very specific data that could not be retrieved in a single query.
I know that it is not efficient, but this runs at night and so it is
more important to have the data accurate and the stored proc easy to
debug.
Thanks
Randy
In article <epadnUGk2aiLNwPdRVn-uQ@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> I would question whether it makes sense to move data around in this
> inefficient sequential manner. Can you create a view rather than copying
> rows into another table? Inserting rows then counting and deleting them
> seems especially pointless. Why can't you write your query (4) to insert
> only the rows you require to start with?
> If you need more help, please post DDL, sample data and show your required
> result.
> http://www.aspfaq.com/5006
>
|||You want (at most) the top 104000 rows by date but only complete months? Try
this:
DECLARE @.dt DATETIME
SET ROWCOUNT 104001
SELECT @.dt = DATEADD(MONTH,DATEDIFF(MONTH,-1,xdate),0)
FROM SomeTable
ORDER BY xdate DESC
SET ROWCOUNT 0
SELECT *
FROM SomeTable
WHERE xdate >= @.dt
I'll be the first to admit that this is far from perfect. It uses a
proprietary, undocumented trick that isn't guaranteed to work in future
versions of SQLServer. I'm sure there are better methods of getting the
result you need but without a full understanding of your business
requirements this is just intended to illustrate one possibility.
David Portas
SQL Server MVP

Is there anything like DoEvents in a stored proc?

Hello,
I have a stored proc that does a series of things
1) create table if doesn't exist else delete all rows
2) select into this table
3) create another table if doesn't exist else delet all rows
4) select large number of records into this table
5) count records
6) delete records if more than a certain number of records
and I also print status in between each for debugging.
I am wondering if there is a way to have the stored procedure give up
some cpu time and also output the print statements before the entire
stored proc has finished.
Thanks
RandyIt seems that your procedure is written ineficiently...
prints are just data and get returned when you come to them.
Probbaly Waitfor Delay will do the job for you.
Bojidar Alexandrov
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||I've used PRINT statements for debugging large SPROCS - they do not come out
in the expected spot at all times.
I assume this is related to the way ADO from VISUAL BASIC has similar
issues - the PRINT statements go into the ERROR COLLECTION of the CONNECTION
OJBECT and seem to wait for RECORDSET processing - can't seem to get both.
In QUERY ANALYZER I've gotten around this issue by sometimes setting the
RESULT PANE to RETURN TEXT as opposed to DATA in a GRID - that way you get
one result window for both the RECORDSET data and the PRINT statements.
I've also at times changed all my PRINT statements to be SELECT statements
for debugging. And in reality if some of those "prints" remain in the
production code, it's better to have them as SELECT statements.
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
I would question whether it makes sense to move data around in this
inefficient sequential manner. Can you create a view rather than copying
rows into another table? Inserting rows then counting and deleting them
seems especially pointless. Why can't you write your query (4) to insert
only the rows you require to start with?
If you need more help, please post DDL, sample data and show your required
result.
http://www.aspfaq.com/5006
David Portas
SQL Server MVP
--|||Thanks for the reply.
The reason I can't just select the records I need in the first place is
because I have to make sure that I have complete months worth of data.
So my logic is something like this.
1) Select top 104000 records into temp table
2) Select count if I have exactly 104000 records then I know that most
likely I have a partial months worth of data so now select the oldest
date, do some date math so that I have the date of the first day of the
next month then delete all records that are older than that.
It is actually even more complicated than that because I am building up
very specific data that could not be retrieved in a single query.
I know that it is not efficient, but this runs at night and so it is
more important to have the data accurate and the stored proc easy to
debug.
Thanks
Randy
In article <epadnUGk2aiLNwPdRVn-uQ@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> I would question whether it makes sense to move data around in this
> inefficient sequential manner. Can you create a view rather than copying
> rows into another table? Inserting rows then counting and deleting them
> seems especially pointless. Why can't you write your query (4) to insert
> only the rows you require to start with?
> If you need more help, please post DDL, sample data and show your required
> result.
> http://www.aspfaq.com/5006
>|||You want (at most) the top 104000 rows by date but only complete months? Try
this:
DECLARE @.dt DATETIME
SET ROWCOUNT 104001
SELECT @.dt = DATEADD(MONTH,DATEDIFF(MONTH,-1,xdate),0)
FROM SomeTable
ORDER BY xdate DESC
SET ROWCOUNT 0
SELECT *
FROM SomeTable
WHERE xdate >= @.dt
I'll be the first to admit that this is far from perfect. It uses a
proprietary, undocumented trick that isn't guaranteed to work in future
versions of SQLServer. I'm sure there are better methods of getting the
result you need but without a full understanding of your business
requirements this is just intended to illustrate one possibility.
David Portas
SQL Server MVP
--

Is there any other faster method to compare and update table?

TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
I must compare these two tables and insert to TABLE3 and update TABLE1.
Is there any other faster method can replace the following method?
Thanks.
---
CREATE PROCEDURE RMSTEST1 AS
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM TABLE1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM TABLE2)
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursorEllen
At first glance a I'd use NOT EXISTS clause to eliminate the rows
SELECT <columns list> FROM Table1
WHERE NOT EXISTS
(SELECT * FROM Table2 WHERE Table1.PK=Table2.PK)
You can insert an output into a temporary table and then to manipulate with
UPDATE statement as you need.
"Ellen" <Ellen@.discussions.microsoft.com> wrote in message
news:EF2570C6-2A36-416E-AF70-BFBB53A20475@.microsoft.com...
> TABLE1 has 5,000,000 records, TABLE2 has 1,000,000 records.
> I must compare these two tables and insert to TABLE3 and update TABLE1.
> Is there any other faster method can replace the following method?
> Thanks.
> ---
> CREATE PROCEDURE RMSTEST1 AS
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM TABLE1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM TABLE2)
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO TABLE3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO TABLE3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE TABLE1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor|||Please include DDL with your posts otherwise we can only guess at your
table structure and exact requirements. Here's an example, assuming B2
is unique in Table2:
INSERT INTO Table3 (/* ... columns list? */)
SELECT T1.a5, ...
CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
FROM Table1 AS T1
LEFT JOIN Table2 AS AS T2
ON T1.a11 = T2.b2 /* B2 is unique? */
--
David Portas
SQL Server MVP
--|||The full script is:
-- Create Tables
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES3]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUSTES4]
GO
CREATE TABLE [dbo].[SUSTES1] (
[A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A28] [int] NULL ,
[A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A30] [int] NULL ,
[A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES2] (
[B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES3] (
[C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[C3] [int] NULL ,
[C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SUSTES4] (
[D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[D2] [int] NULL ,
[D3] [int] NULL
) ON [PRIMARY]
GO
--Import Datat
BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
--Create INDEX
CREATE INDEX PK_B2
ON SUSTES2(B2)
GO
--Main Process
DECLARE tb1_cursor CURSOR
FOR
SELECT A5,A11,A28,A30 FROM SUSTES1
OPEN tb1_cursor
DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF @.V11 IN (SELECT B2 FROM SUSTES2)
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
END
ELSE
BEGIN
INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
END
UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
END
CLOSE tb1_cursor
deallocate tb1_cursor
--Update Table4
INSERT INTO SUSTES4
SELECT C2,SUM(C3),COUNT(*)
FROM SUSTES3
GROUP BY C2
Ellen
"David Portas" wrote:

> Please include DDL with your posts otherwise we can only guess at your
> table structure and exact requirements. Here's an example, assuming B2
> is unique in Table2:
> INSERT INTO Table3 (/* ... columns list? */)
> SELECT T1.a5, ...
> CASE WHEN T2.b2 IS NOT NULL THEN '11110000' ELSE '11120000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN '22220000' ELSE '22230000' END,
> CASE WHEN T2.b2 IS NOT NULL THEN 'D' ELSE 'C' END
> FROM Table1 AS T1
> LEFT JOIN Table2 AS AS T2
> ON T1.a11 = T2.b2 /* B2 is unique? */
> --
> David Portas
> SQL Server MVP
> --
>|||This should take care of the cursor altogether.
INSERT SUITES3
SELECT A5,CASE WHEN B2 IS NULL THEN 10000 ELSE 0 END +I,A30-A28,J
FROM SUITES1 LEFT JOIN SUITES2 ON SUITES1.A5=SUITES2.B2
CROSS JOIN (SELECT 11110000,'D' UNION SELECT 22220000,'C')X(I,J)
-- WHERE A39='N'
-- UPDATE SUSTES1 SET A39='Y'
-oj
"Ellen Huang" <Ellen Huang@.discussions.microsoft.com> wrote in message
news:BCC149AC-6728-4EF1-B42A-E34C21FF5B8C@.microsoft.com...
> The full script is:
> -- Create Tables
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES3]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[SUSTES4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[SUSTES4]
> GO
> CREATE TABLE [dbo].[SUSTES1] (
> [A1] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A2] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A3] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A4] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A5] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A6] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A7] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A8] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A9] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A10] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A11] [varchar] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A12] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A13] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A14] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A15] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A16] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A17] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A18] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A19] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A20] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A21] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A22] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A23] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A24] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A25] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A26] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A27] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A28] [int] NULL ,
> [A29] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A30] [int] NULL ,
> [A31] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A32] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A33] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A34] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A35] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A36] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A37] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A38] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [A39] [varchar] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES2] (
> [B1] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B3] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B4] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B5] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B6] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B7] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B8] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B9] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B10] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B11] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B12] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B13] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B14] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B15] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B16] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B17] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B18] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B19] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B20] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B21] [char] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES3] (
> [C1] [varchar] (13) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C2] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [C3] [int] NULL ,
> [C4] [char] (1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[SUSTES4] (
> [D1] [char] (8) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [D2] [int] NULL ,
> [D3] [int] NULL
> ) ON [PRIMARY]
> GO
> --Import Datat
> BULK INSERT SUSTES1 FROM 'D:\Table1.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BULK INSERT SUSTES2 FROM 'D:\Table2.csv'
> WITH (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
>
> --Create INDEX
> CREATE INDEX PK_B2
> ON SUSTES2(B2)
> GO
>
> --Main Process
> DECLARE tb1_cursor CURSOR
> FOR
> SELECT A5,A11,A28,A30 FROM SUSTES1
> OPEN tb1_cursor
> DECLARE @.V5 CHAR(13),@.V11 CHAR(8),@.V28 INT,@.V30 INT
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF @.V11 IN (SELECT B2 FROM SUSTES2)
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11110000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22220000',@.V30-@.V28,'C')
> END
> ELSE
> BEGIN
> INSERT INTO SUSTES3 VALUES (@.V5,'11120000',@.V30-@.V28,'D')
> INSERT INTO SUSTES3 VALUES (@.V5,'22230000',@.V30-@.V28,'C')
> END
> UPDATE SUSTES1 SET A39='Y' WHERE CURRENT OF tb1_cursor
> FETCH NEXT FROM tb1_cursor INTO @.V5,@.V11,@.V28,@.V30
> END
> CLOSE tb1_cursor
> deallocate tb1_cursor
> --Update Table4
> INSERT INTO SUSTES4
> SELECT C2,SUM(C3),COUNT(*)
> FROM SUSTES3
> GROUP BY C2
>
> Ellen
> "David Portas" wrote:
>