Update code to copy, change, then insert

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)

Update code to copy, change, then insert

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-10-20 : 10:26:16

Hi all,

I've used this code with good luck to replace rows:

Update PR_Employee_master_1_mc
                SET company_code = 'HWP'
                WHERE company_code = 'SCP'

Now, is there a simple way to do the same thing, BUT... not replace the row. As in, I'd like to make a copy of that row of data with the new company_code = HWP, AND keep the old row of data with company_code = SCP. Make sense?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 10:30:06

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-20 : 10:40:21

first do an insert then the update.
or you can do it in an after update trigger to insert old rows again into the table.

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2005-10-20 : 10:43:40

Sure... a very basic example would be this:

Company_Code Employee_Code Employee_Name
SCP 01 Bob
SCP 02 Fred

...now I would like to copy these rows of data, and re-input them as:

Company_Code Employee_Code Employee_Name
HWP 01 Bob
HWP 02 Fred

... and KEEP the old SCP rows, along with the new HWP rows. I didn't know if there was a quick and dirty way of doing this, instead of copying all the data into another TEMP table and Replacing the Company_Code, then reinserting the new data back into the table. Make sense?

Thanks for you help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 10:48:23

Do you need following output?

Old_Company_Code Company_Code Employee_Code Employee_Name
SCP HWP 01 Bob
SCP HWP 02 Fred

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-20 : 10:48:23

you can do this simply with one insert:
insert into table1 (Company_Code, Employee_Code, Employee_Name)
select 'HWP', Employee_Code, Employee_Name
where Company_Code = 'SCP' and other conditions

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2005-10-20 : 10:56:52

Yes I do need to keep the other Input madhivanan. I'll play with that insert code spirit1. Thanks... I have used a method like that in the past, and just didn't know if there was a even a quicker method without having to insert all the tables, but it's no big deal. THANKS to both of you!

hueby
Posting Yak Master

127 Posts

Posted - 2005-10-21 : 16:36:50

This code " you can do this simply with one insert:
insert into table1 (Company_Code, Employee_Code, Employee_Name)
select 'HWP', Employee_Code, Employee_Name
where Company_Code = 'SCP' and other conditions"

Works great..... I'm using it, but for very large tables with lots of columns it takes forever to set them up. Any tips on speeding this process up at all?

I'm currently running a select ALL in QA, results to text, then copying and pasting all the column headers, then inserting COMMAS by hand.. that's it.

Kristen
Test

22859 Posts

Posted - 2005-10-21 : 19:58:33

"for very large tables with lots of columns it takes forever to set them up"


DECLARE	@strColumns	varchar(8000)
SELECT	@strColumns = COALESCE(@strColumns+', ', '') + COLUMN_NAME
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION
SELECT	@strColumns

Kristen

hueby
Posting Yak Master

127 Posts

Posted - 2005-10-24 : 15:13:37

Kristen, wow! THANK YOU FOR THE CODE! That is perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources