How to do bulk insert through my client machine ?

[jacksantho]

Hi,

Am having, some doubt, please clarify this?

1. Using Import/Export wizard, can i able to import the excel data into the temporary table ? How ?

2. The following TSQL is working fine. Can i able to do this, from the CLIENT Machine itself. I don't want to login to my server and going to the D drive, copying this CSV file to get the work done. I don't want this steps.

[B] BULK INSERT ##Updation FROM 'd:\Types.csv' WITH
        (
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n',
		KEEPIDENTITY
	)
[/B]

Please clarify my two doubt. Thanks in advance.

[adam_k]

What file sizes/number of records are we talking about?
The way I see it you can either use a macro with OLEDB and insert the records from excel or create an access db with linked tables (1 for your file and 1 for the sql server) and an insert query.

[jacksantho]

Hi,

just am doing testing with 35 records. I needs to do this task in the client machine , not by going into the server machine. please clarify previous post. thanks

[adam_k]

The ways I've done this kind of thing in the past are:
1) From an Excel macro that reads the (Excel) file and opens a connection to the SQL and inserts what it has read. It's nothing fancy and it works pretty good. Minimum or no maintenance if you've got a few checkpoints (to verify that you are inserting the correct file and not some junk).
2) From an Access db. You can create a linked table to a local file in the client computer (suggest you replace the linked file with the one you wish to insert, so you don't have to do the linked table every time) and a second linked table that will point to your SQL table. Once you have the 2 linked tables in place you can create an insert query to get your data to the server.

Both ways support running a command after the insert is complete, so you can start processing the data you've just inserted without any user input.
If you need code for the Excel I've got to share. For the access I think it's pretty straight forward, but again if you need help just ask.

[jacksantho]

Hi,

Could you please give me some examples to do this. I don't know, how to proceed on these concepts. using BULK INSERT ..is the one we can't achieve our task .thanks for your help

[adam_k]

I'll go with Access as it's simpler and doens't require any understanding of VBA.
Before we begin I suggest you create a folder and save your file in there. When ever you want to upload you'll overwrite that file and save yourself half of this process.
So here we go:
Create a new access db.
Click on the External Data tab in the ribbon.
Click Excel file if you've got Excel or text file if it's CSV. Either way select "Link to the data source by creating a linked table" and then complete the wizard (headers, data types, etc).

Get yourself a DSN (user/machine/file doesn't make a difference) to the SQL db.
In Access go back to external data and click "More" and "ODBC Database".
Again select to Link to the data and complete the wizard. (Choose the DSN, provide username/password and other options and finally select the table you'll link).

Now you have your linked tables ready and need the query that will read from your Excel/CSV linked table into your SQL linked table.
To do this click on the Create tab in the ribbon.
Select "Query design". In the show table dialog add the source table (Excel/CSV) to the query.
Choose Append (You should be in the Design tab already, the option is in the ribbon).
You will be asked where to insert the data. Choose that and map the fields.
Save the query and each time you execute it it will insert your file into SQL.

If you need this to run automatically you can create an autoexec macro that will do the job and close Access.

Have fun.