SQL - how to repeatedly insert records into a table

tommym121
tommym121 used Ask the Experts™
on
Select * into #temp1 from T1

Select * into #temp1 from T1 -- This complain the database object 'Temp1' already existed


How can I keep adding record to the table #temp1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
If you are trying to copy records from one table to another then follow this:

insert into temp1
select * from t1

You can add where clause to the select command based on your requirement if you want to filter the data.
Commented:
The into #temp1 is a fast way to create a #table even without knowing all the columns and datatypes. You try it twice and that's obviously causing problems.

At least the second time you need a insert-statement (as in hafeezmca comment) but you can also create the table first and then use regular insert for both of your inserts
the http://msdn.microsoft.com/en-us/library/ms174335.aspx
Create table #temp 
.....

insert into #temp1 (col1, col2 ...)
select col1, col2 ,.....
from T1

insert into #temp1 (col1, col2 ...)
select col1, col2 ,.....
from T1

Open in new window

Commented:
select * into <tableName> from <yourtable>
always creates new table i.e. <tableName>. So, whenever you run it twice, it fails with the message. If you need to do the same thing, you need to drop the table before running the statement again. But given your requirement, it doesn't looks like that you want to drop and recreate it. Instead you want to insert more data. For that, you need to run the basic insert into tableName statement as the experts above has explained.
Select * into #temp1 from T1
go 400
this command is repeated 400 times

Author

Commented:
Thanks for the information

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial