Insert rows from another table

[TysonLPrice]

I accidentally deleted a set of customer records from four tables. I copied the records from a backup I had and I want to insert them back into production maintaining the identity columns from the backup. I know I have to set the identity column to NO but can't find the syntax to insert all the rows from one table into another. The only examples I see are providing by the values. Anyone know how to do this.

I'm using SQL 2000.

 

[mimi2]

INSERT INTO table??
(number ,date)
SELECT
p.number ,m.date
FROM test p INNER join test_m m
on m.test_id = p.test_id
WHERE (id = @id ) ORDER BY p.number, m.date

 

[TysonLPrice]

I don't want to specify values. Just a straight table to table insert. Kind of like:

Insert into table1 (select * from table2)-this doen't work

The rows I want to insert were deleted from the target table. Is there anything like that?

 

[mimi2]

i used something like this in the past:
insert into table2
(id,name)
select
id, name
from table1
where id in
(select id from table1 where id not in
(select id from table2)
)

 

[JohnDTampaBay]

Take away the parentheses, and you've just about got it.

INSERT INTO Table1 SELECT * FROM table2

The column order and data types must be the same in both tables. If you have an IDENTITY column in Table1, then you'll need to SET INDENTITY_INSERT Table1 ON prior to the INSERT statement and you will have to specific the column names. There are probably a few other caveats that I've missed. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[TysonLPrice]

I guess I'm not stating what I want to do clearly. I want to copy the rows from one table into another on with an identical structure using SQL. I have four tables I have to do this for and they have hundreds of column names. I don't want to name the columns.

I don't mean this to sound abrupt. I'm still new at all this and sometimes I'm not using the right terminolgy.

 

[TysonLPrice]

AngelWPBTampa,

Thanks for the reply. I made my last post beofre I saw yours. I tried this:

SET IDENTITY_INSERT appts on
INSERT INTO appts SELECT * FROM apptsrestore

and got this:

Server: Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'appts' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I guess I need to specify column names.

 

[mimi2]

from the object browser in query analyzer, you can drag all columns at once and drop them where you want.
you dont have to type them one by one

 

[JohnDTampaBay]

Yes, you will have to specific the column names. Mimi2 has a good idea about the column names. In the Object Browser, expand the table name then expand columns so you can see the list. Then drag and drop the Columns folder (not the individual column names) in a query window. You should get a comma-separated list of the columns.

Also, after insertion don't forget to SET IDENTITY_INSERT appts OFF.



--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[TysonLPrice]

Thanks everyone,

The drag and drop tip takes the pain out of it.