Copy - 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

General SQL Server Forums

New to SQL Server Programming

Copy

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-19 : 14:25:11

How do you copy a chunk of data from a table......and then change the Primary key and other columns......and then paste into the table you copied from.

Normaly i would build a table from scratch in excel but I am certian there is a better way.

Best,
Graham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:29:22

[code]
insert into yourtable (<all columns except pk column>)
SELECT <all columns except pk column>
FROM yourtable
[/code]

I'm assuming your pk column is an identity
also in above case you'll duplicating all existing data in table with new pk values.

if this is not what you want, please specify rules of how you want to change the data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-19 : 14:45:54

Do you think this will to the trick? My concern is that it will duplicate the programid between 1331 and 1420 and then update all of them even the ones I want to leave. Let me know what you think, and thanks for the help.

GG

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-19 : 14:46:22

INSERT INTO FEE (ProgramID, CoverageID, FeeAmount, CalcType, TaxPolicyFee, RateEffectiveDate, RateExpirationDate)
Sorry here it is

(SELECT rogramID, CoverageID, FeeAmount, CalcType, TaxPolicyFee, RateEffectiveDate, RateExpirationDate
FROM Rating_2.dbo.Fee
WHERE (ProgramID BETWEEN 1331 AND 1420)---SC
update ratedata
set Programid = programid + 492
Where programid between 1331 and 1420

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:49:08

why do you do it in two steps then? wont this be enough?


INSERT INTO FEE (ProgramID, CoverageID, FeeAmount, CalcType, TaxPolicyFee, RateEffectiveDate, RateExpirationDate)
(SELECT ProgramID+492, CoverageID, FeeAmount, CalcType, TaxPolicyFee, RateEffectiveDate, RateExpirationDate
FROM Rating_2.dbo.Fee
WHERE (ProgramID BETWEEN 1331 AND 1420)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-19 : 14:51:37

You are my hero!!!!!!!!!!!! Awesome man thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 14:52:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-10-19 : 16:17:33

Ok will that insert statement work in this table too. I am trying to copy all of SC rows back into table and then change the state to AL and add 400 to SC id. With this query work? thanks for the Help!

Insert INTO Policyforms (ID, PolicyTypeID, TransactionType, CompanyNum, State , FormName, FormNumber, FormDescription, Mandatory, TypeOn, CovID_Print, CovID_NoPrint,
OccupancyType, RCVID, GroundFloor, PackageTypeID, FormType, EffectiveDate, ExpirationDate
(select ID+400, PolicyTypeID, TransactionType, CompanyNum, State = 'Al' , FormName, FormNumber, FormDescription, Mandatory, TypeOn, CovID_Print, CovID_NoPrint,
OccupancyType, RCVID, GroundFloor, PackageTypeID, FormType, EffectiveDate, ExpirationDate
FROM Rating_2.dbo.PolicyForms
Where policyforms.state = 'SC' and programid between 1331 and 1420)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:42:05

quote:
Originally posted by JJins

Ok will that insert statement work in this table too. I am trying to copy all of SC rows back into table and then change the state to AL and add 400 to SC id. With this query work? thanks for the Help!

Insert INTO Policyforms (ID, PolicyTypeID, TransactionType, CompanyNum, State , FormName, FormNumber, FormDescription, Mandatory, TypeOn, CovID_Print, CovID_NoPrint,
OccupancyType, RCVID, GroundFloor, PackageTypeID, FormType, EffectiveDate, ExpirationDate)
select ID+400, PolicyTypeID, TransactionType, CompanyNum, 'Al' , FormName, FormNumber, FormDescription, Mandatory, TypeOn, CovID_Print, CovID_NoPrint,
OccupancyType, RCVID, GroundFloor, PackageTypeID, FormType, EffectiveDate, ExpirationDate
FROM Rating_2.dbo.PolicyForms
Where policyforms.state = 'SC' and programid between 1331 and 1420

it should with little modification above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources