SQL Bulk Insert

Question

Post reply

SQL Bulk Insert

leonp

SSCommitted

Points: 1962
More actions
January 16, 2009 at 11:53 am

#126765

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

leonp SSCommitted Points: 1962 More actions · Answer 2

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?????

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Does the target table exist to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leonp SSCommitted Points: 1962 More actions · Answer 4

lets assume we get to choose that ..... what are our options???

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leonp SSCommitted Points: 1962 More actions · Answer 6

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!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)