Bulk import on SQL with Format File

Quick fire way to get new data into a SQL table using bulk load:


C:\>bcp thedatabase.dbo.thetable  format nul -t -n -f thetable.fmt -U  yourusername  -P yourpassword


USE thedatabase
GO
BULK INSERT thetable
 
   FROM 'C:\thetabelnewdata.dat' 
   WITH (FORMATFILE = 'C:\thetable.fmt');
GO

SELECT * FROM thetable

go

How does the .fmt file look? (http://msdn.microsoft.com/en-us/library/ms191516.aspx) Lots of different values, but here is one example

10.0
9
1 SQLCHAR 0 1 "\"" 0 first_double_quote SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "\",\"" 1 ip_from ""
3 SQLCHAR 0 20 "\",\"" 2 ip_to ""
4 SQLCHAR 0 2 "\",\"" 3 country_code SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 64 "\",\"" 4 country_name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 128 "\",\"" 5 region_name SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 128 "\",\"" 6 city_name SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 20 "\",\"" 7 latitude ""
9 SQLCHAR 0 20 "\"\r\n" 8 longitude ""


Which loads data for into this table

CREATE TABLE [ip2location_db5_newfile](
[ip_from] float NOT NULL,
[ip_to] float NOT NULL,
[country_code] varchar(2) NOT NULL,
[country_name] varchar(64) NOT NULL,
[region_name] varchar(128) NOT NULL,
[city_name] varchar(128) NOT NULL,
[latitude] float NOT NULL,
[longitude] float NOT NULL,
)
GO

(used for storing data purchased from http://www.ip2location.com/)




Comments

Post a Comment

Popular posts from this blog

Global SQL Procedure, System Objects and sp_ms_marksystemobject

You may come across a need to have a database of utils full of generic procedures which work on indvidual databases (say client databases). You can't pass the database name into the procedure as a parameter and say "use @dbname" in the procedure and dynamic sql sucks. One workaround is to create the procedure in the master database and then mark it as a system object. eg use [master] create procedure sp_doThis // note the sp_ prefix is required begin // etc etc end go exec sp_ms_marksystemobject 'sp_doThis' // second note, this procedure is undocumented, so I wouldn't be relying on this for life or death. use [myotherdb] exec sp_doThis go All done!
Read more

cf_sql_timestamp vs cf_sql_date vs getdate()

If there's one thing I don't like it is people confusing a date with a timestamp, and how a lazy bit of development can ruin a load of data. Here is a simple query inserting data to a timestamp I found in a system where all the created dates were truncated because of the incorrect syntax. Bad one which was in use: insert into testdate values (<cfqueryparam cfsqltype="cf_sql_date" value="#createodbcdatetime(now())#">); Result :  2015-09-29 00:00:00.000 Best One (easiest to read) insert into testdate values(getdate() ); Result : 2015-09-29 10:10:09.880 Not great (no binding) insert into testdate values(#createodbcdatetime(now())#); Result :  2015-09-29 10:14:44.000 What the bad one should have been insert into testdate values(<cfqueryparam cfsqltype=" cf_sql_timestamp " value="#createodbcdatetime(now())#">)
Read more

Lucee 4.5.2 cfpdfparam difference with Adobe ColdFusion

Suppose the following on Lucee 4.5.2: <cfset x = 1> <cfdocument format="pdf" pagetype="A4" name="myVar#x#"> <cfdocumentsection> Hi there </cfdocumentsection> </cfdocument> <cfpdf action="merge" destination="RAM:///myPDF.pdf" overwrite="yes">     <cfpdfparam source="#evaluate("myVar#x#")#" /> </cfpdf> <cfcontent file="RAM:///myPDF.pdf" type="application/pdf"> then to check it also runs on Adobe Cf; you'd actually end up with: "ByteArray objects cannot be converted to strings."   The original code for Adobe CF ran with the paramater like so:  <cfpdfparam source="myVar#x#" /> Both sets of documentation list source defined as follows: "Source PDF file to merge. You can specify a PDF variable, a cfdocument variable, or the pathname to a file." This would indicate it is just...
Read more