stored procedure code help - SQL Server Forums

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)

stored procedure code help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 10:09:38

i'm trying to do the following in a stored procedure can you help me

i have a @reg which has the reg code
i want to select all records from tempcustomers where reg=@reg and move those records to customers adding on 2 fields (date and batch)

can you help me with the syntax? how can i loop through a table moving it in sql?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-07 : 10:13:39

move as in INSERT or UPDATE ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 10:29:26

[code]Declare @dt datetime, @batch int

select @dt = getdate(), @batch = 1 -- set desired values here

Insert customers(col1, col2, [date], batch)
Select col1, col2, @dt, @batch
From tempcustomers
where reg=@reg[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 11:06:50

this will loop thorugh and do all those with that reg? (even if there is more then one?

also I waanted to move not copy so how do I insert and then delete that record?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-07 : 11:14:30

Yes it will move all records. If you want to delete those records from original table, add delete statement after insert with same WHERE clause.

And be sure to put both of them inside transaction !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-07 : 12:01:57

CREATE PROC MOVE @sql varchar(8000).....just kidding

"moving" data is losing it's historical value, never mind having potentially redundant data

Why can the same type of data be in 2 different spots?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 12:13:20

so can i update the record in tempcustomers so i know that i moved it?

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-07 : 12:17:59

my point is, why move it at all. Why not have a status colum to determine what state the data is in?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-07 : 12:26:48

yes so how do i update a status collumn at the same time as moving

Insert customers(col1, col2, [date], batch)
Select col1, col2, @dt, @batch
From tempcustomers
where reg=@reg

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-07 : 13:52:36

You don't. You leave it where it is and update the status column in the table it's already in...think of it as a "logical" move

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources