CODEWITHSUNDEEP
sql-serverimportssmsbulkinsert
Tim Wilcox
Tim Wilcox

Reputation: 1331

How to import data into SQL Server without using import and export wizard

I have a very large number of text files to import into SQL Server. I could use the import and export wizard and then have to manually adjust column names and data types.

I have been looking around some of the options given are bcp and Bulk Insert. I follow the code given (https://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/) and get nowhere. Below is a code snippet that I have tried. 

 BULK INSERT dbo.industry
 FROM C:\Users\username\Documents\Industry201603
 WITH (fieldterminator = ',', Firstrow = 1)

First question: what is the easiest way to import from the local machine (C:\ drive) into SQL Server?

Second question: not specifying column names and data types in the import process, is this a problem?

Upvotes: 0

Views: 2490

Answers (2)

ASH
ASH

Reputation: 20302

Use Dynamic SQL and loop through the files.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=48)
BEGIN

PRINT @intFlag


declare @fullpath1 varchar(1000)
select @fullpath1 = '''\\your_path_here\' + convert(varchar, getdate()- @intFlag , 112) + '_.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[Daily] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)

I used this technique several years ago, for a daily data load, and it worked perfectly fine. In this example, the file name has the date in it, so I'm looping through all files, identifying each by the date.

Upvotes: 1

user6906550
user6906550

Reputation:

I did this once by right clicking and then selecting Edit top 200 rows and copying and pasting. I couldn't get any of it to work, but something so simple did.

I suppose that might not work if you have a lot of data but it did do the job for me.

Upvotes: 0

Related Questions