Click here to Skip to main content
15,070,111 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL

I have daily files that need to be appended to the same sql table. I know how to use the "SQL Server Import and Export Wizard" but I need to add the "file name" and "date/time" to the end of each daily file I import(...so we can distinguish the records as to when and which file they came from).

Question 1: Is the "SQL Server Import and Export Wizard" the best way to import these files?

Question 2: How do I add the File Name and the Import Date/Time the imported file? Note: I will be append the daily files to the table.

The is usually very little on the web or so much it's like asking someone what time it is and they tell how to build a watch. :)
Posted

1 solution

Try to use OPENROWSET[^].
SQL
DECLARE @tname VARCHAR(150)
DECLARE @fname VARCHAR(150)

SET @tname = 'c:\test\File1.txt'
SET @fname = 'c:\test\File1.fmt'


INSERT INTO SQLTable (Field1, Field2, ..., FileName, ImportDate)
SELECT a.Field1, a.Field2, ..., @tname AS FileName, GETDATE() AS ImportDate
FROM OPENROWSET( BULK , @tname, FORMATFILE = @fanme) AS a;


Please, visit above link get more details ;)
   
Comments
Runner55Code 22-Nov-13 12:01pm
   
Wow...excellent. Thanks
Maciej Los 22-Nov-13 12:04pm
   
You're very welcome ;)
Please, accept it as an answer (green button).
Runner55Code 22-Nov-13 13:22pm
   
DECLARE @tname VARCHAR(150)
DECLARE @fname VARCHAR(150)

SET @tname = 'C:\DataImport'
SET @fname = 'ImportMe.csv'

INSERT INTO TryTest
(
RecordDate,
EmailAddress,
NameFirst,
NameLast,
Addr1,
Addr2,
AddrCity,
AddrState,
AddrZip,
Phone,
Production,
AdvisorType,
PlacementID,
currentFileName,
ImportDate
)
SELECT
[Record Date],
[Email Address],
[First Name],
[Last Name],
[Street Address],
[Address 2],
City,
State,
[Zip/Postal Code],
PHONE,
Production,
[Advisor Type],
[Placement ID],
@Fname AS FileName,
GETDATE() AS ImportDate
FROM OPENROWSET( BULK , @tname, FORMATFILE = @fanme) AS a;

It doesn't like the comma after BULK and it doesn't find and of the field names after the Select.

Msg 207, Level 16, State 1, Line 26
Invalid column name 'Record Date'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'Email Address'.
Msg 207, Level 16, State 1, Line 28
Invalid column name 'First Name'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'Last Name'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Street Address'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'Address 2'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'City'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'State'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'Zip/Postal Code'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'PHONE'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'Production'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'Advisor Type'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'Placement ID'.

What am I doing wrong.
Maciej Los 22-Nov-13 13:29pm
   
The column names must be equal (INSERT and SELECT section). Please see the online documentation to properly set OPENROWSET options for csv files.
Runner55Code 22-Nov-13 13:36pm
   
Will do. Sometimes they instructions on the web can be very confusing. I wish I knew where to go that would explain all the { [ etc. Sometimes simple examples go along way!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900