Another stored procedure question...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Another stored procedure question...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 11:48:32

I'm back

Here's the background again...

I have to compare last week's table (NewDiary9_22)to the new week's table (NewDiary9_29) If PAN is in NewDiary9_22 and not in NewDiary9_29 it should go to the cleared table and the rest to the Pending Table.

I want to keep all weekly data in one table, just need an extra
column to store the week start date. This way I don't have to create a new table every week.

How do I alter this stored procedure to reflect that.
Right now I have to change my queries every week to reflect the
table name change

SELECT t1.*
FROM NewDiary9_22 t1
left JOIN NewDiary9_29 t2
ON t1.PAN=t2.PAN and t1.recno = t2.recno
WHERE t2.PAN IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:24:01

You mean modify above code to use a single table? where will your new data come from then?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 12:31:05

Yes I only want to use one table is this possible? I will bring in the the new data from a flat file once a week. I need to bring in the new data grab what I want and delete the other that I don't want then do a comparasion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:50:40

quote:
Originally posted by JJ297

Yes I only want to use one table is this possible? I will bring in the the new data from a flat file once a week. I need to bring in the new data grab what I want and delete the other that I don't want then do a comparasion.

ok. then what you could do is to populate data from file onto a temporary table and thenuse it to compare to your table and do the insert and deletions. The initial file to table import can be done using Export Import wizard,OPENROWSET....

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 13:46:55

I actually need a stored procedure to do this as I will be putting this in SSIS and having it run as a job. Can you help me write a stored procedure to do this?

I looked through one of my old posts and you gave me this don't know how to set mine up.

SELECT t1.*
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

and for deleteing

DELETE t1
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:56:59

Which ones do you want to delete and which ones to insert/update?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 14:05:57

I want to delete from NewDiary9_22 (last week Diary) and insert into NewDiary9_29.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 14:08:58

quote:
Originally posted by JJ297

I want to delete from NewDiary9_22 (last week Diary) and insert into NewDiary9_29.

ok so delete from table and insert from file under new case?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 14:25:27

Yes

Something like

Compare the two files and delete the records from NewDiary9_22 if PAN is not in NewDiary9_29.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 14:31:41

[code]DELETE t1
FROM NewDiary9_22 t1
left JOIN NewDiary9_29 t2
ON t1.PAN=t2.PAN
WHERE t2.PAN IS NULL[/code]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 14:32:04

Can I do something like this:

SELECT t1.* into Cleared (going into a new table)
FROM NewDiary9_22 t1
left JOIN NewDiary9_29 t2
ON t1.PAN=t2.PAN
WHERE t2.PAN IS NULL

Delete * from t1
where t1.PAN
HAVING COUNT(*) => 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 14:56:22

quote:
Originally posted by JJ297

Can I do something like this:

SELECT t1.* into Cleared (going into a new table)
FROM NewDiary9_22 t1
left JOIN NewDiary9_29 t2
ON t1.PAN=t2.PAN
WHERE t2.PAN IS NULL

Delete * from t1
where t1.PAN
HAVING COUNT(*) => 1

you can. make sure you drop cleared before running this query each time. else it will error on subsequent runs.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 14:59:45

How do I put this in the same stored procedure? What do you mean Drop cleared? I am actually going to do this in SSIS and want to put the stored procedure in an Execute SQL Task.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 15:03:36

quote:
Originally posted by JJ297

How do I put this in the same stored procedure? What do you mean Drop cleared? I am actually going to do this in SSIS and want to put the stored procedure in an Execute SQL Task.

ok. then a put another execute sql task before this to drop the table.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-08 : 15:11:49

Let me try this...

How would I write this?

compare the two tables and delete * from NewDiary9_29 if PAN is not in NewDiary9_22
where PAN = PAN

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-08 : 18:27:21

just put "CREATE PROC prc_name" around your post

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 00:59:34

quote:
Originally posted by JJ297

Let me try this...

How would I write this?

compare the two tables and delete * from NewDiary9_29 if PAN is not in NewDiary9_22
where PAN = PAN

DELETE t1
FROM NewDiary9_29 t1
left JOIN NewDiary9_22 t2
ON t1.PAN=t2.PAN 
WHERE t2.PAN IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources