Create (nearly) duplicate rows

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 2000 Forums

SQL Server Development (2000)

Create (nearly) duplicate rows

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2005-09-20 : 21:53:09

I'm looking to create a feature on our site via a stored procedure that takes certain rows in a table, reads their content and creates nearly identical new rows. Basically like that of a "Copy" button. I know how I could do it with a cursor but I try to avoid cursors unless there's no other way.

The only difference between these newly copied rows and the original ones would be a field on the table which contains the current date. The original rows would keep their old date while the new ones would have a current date.

So in other words, lets say my table had a field called "customerID" and I wanted to create a copy of all rows that had a customerID = 23499 but obviously place the current date into the date field.

Any idea on how to do this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-20 : 22:36:00

I think a simple INSERT statement would do the job.


insert into TableName
	(
	col1,
	col2,
	...
	DateCol
	)
select
	col1,
	col2,
	...
	-- Populate DateCol with current date at midnight
	DateCol = dateadd(dd,datediff(dd,0,getdate()),0)
from
	TableName a
where
	a.customerID = 23499

CODO ERGO SUM

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2005-09-21 : 14:29:39

That did it.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-21 : 15:17:57

What's with DATEADD and DATEDIFF?

Just use

DateCol = GetDate()

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-21 : 15:39:05

He said in his post that he wanted the current date, so I assumed that he meant a date without the time.

I set it up for the date with the time at midnight, and said that in my comment.

quote:
Originally posted by X002548

What's with DATEADD and DATEDIFF?

Just use

DateCol = GetDate()

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

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources