Select data to INSERT into another table.

[bberrios]

I have been trying this statement:

SELECT * FROM REPORT
WHERE AGENCY_ID = 6

From ther I want to input into a duplicate table.

How can I do this? I have tried several ways...but no luck.

Thanks! bb

 

[Corran007]

Insert into newtable(Col1,col2,etc) SELECT * FROM REPORT
WHERE AGENCY_ID = 6

That should work i think for an existing one

if you just want a new one created

SELECT * into NewTableName FROM REPORT
WHERE AGENCY_ID = 6

Will be a copy of the old one.

 

[Guest_imported]

I had tried that earlier, but I must have had the syntax wrong somewhere. But, as an note it would not allow me to SELECT *, i had to SELECT each column name in the select statement.

BB

 

[MeanGreen]

When you insert using a SELECT statement, if all columns from your select exists in your other table and they are in the same order of columns, then you should not have to list each column. In this case the select * would work. You must have all columns in the inserted table to use the Select * statement when inserting, otherwise, as you have found out, you have to list each column you want to insert. I hope this helps explain why in your case the select * probably did not work.