copy table into existing table

[michelleqw]

Dear sql server users,

We are trying to copy a selecting into a existing table (both have the same structure)

For making a new table we are using:

select * into newtable from existingtable

This goes well if newtable doesn´t exist, if the newtable already exist an error will occur.

Is there any solution for this problem?

Michelle.

 

[dbomrrsm]

if the tables have the same structure then I think you can do a:

INSERT INTO MYTABLE
SELECT * FROM MYOTHERTABLE

this will append records into MYTABLE

[blue]DBomrrsm[/blue]

 

[bboffin]

If you want to clear out any existing data first then use:

TRUNCATE TABLE MyTable

before the INSERT.

Alternatively your original code would be OK if you DROP the table first. You'll need to check that it exists first of course. To see how this is done use SQL Enterprise Manager to generate a SQL script for the table and make sure that the check box to DROP the table is ticked. The first couple of lines show how to drop the table if it exists.



Bob Boffin

 

[michelleqw]

Dear all,

The existing table must keep tha data!!

The only thing we want is to copy data into the existing table.

Michelle.

 

[dbomrrsm]

are the two tables structured the same i.e. number of fields - datatypes etc if so the select into I posted above will work. It will leave existing data and put in the new data.

[blue]DBomrrsm[/blue]

 

[SQLSister]

DBomrrsm is correct. However I would use the form that includes the actual field names to make sure it doesn't break if you add a field to either table (but not both)later on. If you have an identity field in the main table his form won't work either as an identity must be omitted formthe field list.
The form of the statment I prefer:

insert table1 (field1, field2)
Select Field1, field2 from table2

Questions about posting. See faq183-874

 

[michelleqw]

Hello SQLSister,

Thats the right syntax where we are searching for.

Thanks,

Michelle.