I'm still getting used to SQL, so before I get to using stored procedure, I would like to understand how to use BULK INSERT effectively first.
I need to combine 50+ csv files and dump them into an SQL table. The problem is, I'd like to be able to tell each record apart (as in, each record belongs to a certain csv file, which I will identify by the file name).
Here's a small example of what I want:
CREATE TABLE ResultsDump
(
PC FLOAT,
Amp VARCHAR(50),
RCS VARCHAR(50),
CW VARCHAR(50),
State0 VARCHAR(50),
State1 VARCHAR(50),
)
BULK INSERT ResultsDump
FROM 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT ResultsDump
FROM 'c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT ResultsDump
FROM 'C:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT ResultsDump
FROM 'c:\distance1000_7_13_2010_3_21PM_Avery DennisonAD_230000B_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
BULK INSERT ResultsDump
FROM 'c:\distance1000_7_13_2010_3_41PM_Avery DennisonAD_230000C_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
I know this is an inefficient way of doing things, but I definitely like to figure out how to manually dump one file in the SQL table in the format I want before I start to create a stored procedure.
In the new table, I want something like this:
FileName,PC,Amp,RCS,CW,State0,State1
c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv, ...
...
...
c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv, ...
...
...
c:\distance1000_7_13_2010_2_58PM_Avery DennisonAD_230000A_10S_Lock.csv, ...
...
...
Any simple suggestions or referrals to specific functions would be great! Remember, I'm getting used to SQL and it'd be great if I could take this one step at a time, that's why I'm starting with such a simple question.
Thanks in advance!
It is possible to use optional terminating characters to mark the end of a field or row, separating one field or row in the data file from the next. Terminating characters indicate to a program reading the data file where one field or row ends and another begins.
BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process. For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).
To execute a large script:On the Database menu, click Execute Large Script. In Connection, select a connection to a required database server against which you want to execute your script. In Database, select a required database from the drop-down list.
You can add a column FileName varchar(max)
to the ResultsDump table, create a view of the table with the new column, bulk insert into the view, and after every insert, set the filename for columns where it still has its default value null
:
CREATE TABLE dbo.ResultsDump
(
PC FLOAT,
Amp VARCHAR(50),
RCS VARCHAR(50),
CW VARCHAR(50),
State0 VARCHAR(50),
State1 VARCHAR(50),
)
GO
ALTER TABLE dbo.ResultsDump ADD [FileName] VARCHAR(300) NULL
GO
CREATE VIEW dbo.vw_ResultsDump AS
SELECT
PC,
Amp,
RCS,
CW,
State0,
State1
FROM
ResultsDump
GO
BULK INSERT vw_ResultsDump
FROM 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
UPDATE dbo.ResultsDump
SET [FileName] = 'c:\distance1000_7_13_2010_1_13PM_Avery DennisonAD_2300008_10S_Lock.csv'
WHERE [FileName] IS NULL
BULK INSERT vw_ResultsDump
FROM 'c:\distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
UPDATE dbo.ResultsDump
SET [FileName] = 'distance1000_7_13_2010_2_27PM_Avery DennisonAD_2300009_10S_Lock.csv'
WHERE [FileName] IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With