In Access It Was Make Table...

[DomDom3]

Hi there,

I'm Using an Access front end and I'd liek to insert the data from a view into a table.

Before I upsized I used Make Table, but I'm unsure now how the best way to go about it in SQL Server.

The table already exists I'd like to clear the contents then copy in the view.

Is it best to use truncate to clear the table, then use Update to add the View's data. I have tried this but it didn't like the fact the table already existed.

Or would it be better to Create table, in which presumably is the longwinded method as there are many fields.

Any advice would be appreciated.

 

[Gixonita]

Hello,

It the table already exists in SQL Server then you can

TRUNCATE TABLE <Your Table>

INSERT INTO <Your Table>
SELECT * (Or field1, field2,.... fieldN)
FROM <Your View>

I don't think its necessary to drop and recreate the table.

Hope this helps

 

[DomDom3]

Thanks for your response, I'd previously tried this method but I received an error message saying that I couldn't insert into the table because the control already existed.

In the end I opted for a drop table and then an insert.

 

[djj55]

Note if you have an identity field you may need to use a variation of the INSERT INTO

INSERT INTO <Your Table> (field2, field3, ...)
SELECT ...

Hope this helps
djj

 

[AlexCuse]

If you are still getting an error message you might try this:

TRUNCATE TABLE <Your Table>

SET IDENTITY_INSERT <Your Table> ON

INSERT INTO <Your Table>
SELECT *  --(Or field1, field2,.... fieldN)
FROM <Your View>

SET IDENTITY_INSERT <Your Table> OFF

This might do the trick.

Hope it helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[djj55]

Alex - I forgot that one.
djj