Copy one table values to another

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)

Copy one table values to another

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-07-10 : 18:09:18

My dumb butt is back. Hope you guys are all doing well and having a good summer.
I am having a tad of a problem with just trying to copy info from one table to another. Simply want to do a backup for that day. To fall back on if there was a mistake. With me, that is likely.

CREATE PROCEDURE CreateSBBackupProc AS
DROP TABLE SBBackup
CREATE TABLE SBBackup (IDNum SMALLINT,
Vr VarChar(50),
VO SMALLINT,
He VarChar(50),
HO SMALLINT,
Total DEC(3,1),
Uv SMALLINT,
Un SMALLINT,
GTime SMALLDATETIME,
PRIMARY KEY (IDNum))
Insert into SBBackup(IDNum,Vr,VO,He,HO,Total,Uv,Un,GTime) values (IDNum,Vr,VO,He,HO,Total,Uv,Un,GTime) from SB8
GO
I have tried variations of the above. The tables are identical, except for table name of course. Any help, thanks. As always.
John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-10 : 18:12:18

INSERT INTO SBBackup
SELECT *
FROM SB8

You could also use an explicit column list:

INSERT INTO SBBackup (Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM SB8

Tara

John T.
Posting Yak Master

112 Posts

Posted - 2003-07-10 : 18:27:55

Thanks very much Tara.

John T.
Posting Yak Master

112 Posts

Posted - 2003-07-10 : 20:57:38

My first sproc worked just fine. The following one is giving me an error :
CREATE PROCEDURE CreateDTBackupProc AS
Drop Table DTBackup
Create Table DTBackup (CName varchar(50),
GaNo varchar(50),
Sp varchar(50),
Type varchar(50),
Ga varchar(50),
Sel varchar(50),
Os SmallInt,
Un SmallInt,
GTime SmallDateTime,
Tot dec(3,1),
VS SmallInt,
HS SmallInt,
Special SmallInt,
AbbrvSel varchar(50),
Primary Key (CName,GaNo,Sp,Type))

Insert into DTBackup
Select * from DailyTable
GO
It tells me that it disallows implicit conversion from data type smalldatetime to data type decimal. I don't understand because I am basically doing the same thing with this guy as the one prior.
And I drop the table in the prior sproc also.
Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-10 : 21:57:46

Your columns are not lining up.

Don't do this:

Insert into DTBackup
Select * from DailyTable

Rather, specify the exact columns you are inserting INTO and which columns to select FROM:

insert into DTBackup (c1, c2, c3 ....)
select c1, c2, c3 ...
From DailyTable

- Jeff

John T.
Posting Yak Master

112 Posts

Posted - 2003-07-10 : 22:54:00

Thank you sir. Appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources