January 16, 2009 at 11:53 am
Is there a way one could do BULK INSERT from table to table? meaning bulk insert the output of a query into another table?
is that possible? All I could find was bcp to a flat file and then BULK INSERT/bcp into a table .. is there a way to save the detour?
January 16, 2009 at 1:59 pm
Check Books On Line (BOL) for INSERT INTO.
Assuming the table you are inserting into exists then you could use
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeOtherTable
If the table into which data is to be inserted does not exist then look
at SELECT INTO rembering that
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list,
SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)
Technically these are not BULK inserts but as I understand your question would move the data as you desired in one step, but should create entries in your transaction log. If my assumption as to what you are attempting to do is incorrect -- say so and someone will help you with a true BULK insert.
January 16, 2009 at 2:06 pm
bitbucket - thanks for getting back - but you were right about your assumption of being not right -
I was looking to see if someone knew of a way to do an INSERT INTO one table SELECT * from another while logging the transaction minimally
.... anybody?????
January 16, 2009 at 4:59 pm
Does the target table exist to begin with?
--Jeff Moden
January 16, 2009 at 6:07 pm
lets assume we get to choose that ..... what are our options???
January 17, 2009 at 3:08 pm
Sorry... I asked the wrong question... does the target table have any data in it that you actually want to keep? If it does, then you can't get true minimal logging more than what the SIMPLE recovery mode offers.
The only way I know of to do minimal logging is either for the table to be empty and do a Bulk Insert or BCP from a file (along with a wad of other conditions) or to create a new table using SELECT/INTO.
--Jeff Moden
January 17, 2009 at 6:57 pm
No, the target table does not have data - it is empty when the insert into is issued.
I was aware of bcp - and i tested the performance achieved by bcping out of the table, and then bcping the file back into the table - this was still quicker than the regular logged insert. However, this detour for the data has two additional steps, requires more space.
Did you indicate Select/Into involves minimal logging?
Thanks again!
January 17, 2009 at 7:22 pm
Yes... Select/Into will do minimal logging under the right conditions. But, it also builds the table... you would have to drop the target table first.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply