Want to duplicate table before dropping it 2

[azoe]

I have a stored procedure that creates table A and fills it with data each month so that my reports can run from table A.

Now they would like to see this month's reports start out with last months ending balance (one of the fields in table A). That is a calculated field and not stored in the database so I was thinking I would just copy table A to table B before dropping Table A each month.

This is something I have never done before - the other stored procedure was already there. Would it be best to create a stored procedure that runs first and drops table B then creates table B and does an update query to fill it with data from table A (before table A gets dropped and recreated)?

I mean, there's not an easier way like a copy table command or something is there? I looked in SQL Server books online but didn't see anything like that.

And how do you do an update query to update one table from another? Is it:
Update Table B
set field1 = tableA.field1 ?

I don't really have anything for the Where since I just want to load everything into Table B from Table A.

Thank you

 

[gmmastros]

You could drop TableB and then use the Select into command.

Drop TableB

Select FieldList
Into TableB
From TableA



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Mongr1l]

As for a quick method of copying a table:

SELECT *
INTO TABLE_A_COPY
FROM TABLE_A.


As for updating from a joing:

UPDATE B
SET B.FIELD1 = A.FIELD1
FROM TABLE B, TABLE A
WHERE B.ID = A.ID


How's that?

- mongr1l

 

[azoe]

George and mongr1|

that is fabulous

that certainly saved me a lot of work from what I was planning.

thanks so much