Full-Stack freelance Umbraco developer in the UK.

George Phillipson - Freelance Umbraco developer

Insert Data From text file using a template

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  
  <FIELD ID="01" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="02" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="03" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="246" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="04" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="05" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="256" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="06" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="01" NAME="CountryID" 		xsi:type="SQLINT" NULLABLE="YES"/>
  <COLUMN SOURCE="02" NAME="LanguageCode" 	xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="03" NAME="CountryName" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="04" NAME="CountryCode" 	xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="05" NAME="Transliteration" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="06" NAME="ContinentID" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  </ROW>
</BCPFORMAT>
CountryID|LanguageCode|CountryName|CountryCode|Transliteration|ContinentID
ALTER PROCEDURE [dbo].[spInsertCountryList]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
MERGE [dbo].[tblCountrylist] AS Target
USING (SELECT * FROM OPENROWSET(BULK 'F:\EAN\countrylist.txt',
	  FORMATFILE='F:\EAN\countrylist.xml', FIRSTROW = 2) AS BCP) AS Source
-- primary key to find matching records
ON Target.CountryID = Source.CountryID
-- UPDATE RECORD
WHEN MATCHED THEN UPDATE SET 
	 Target.LanguageCode		= Source.LanguageCode, 
	 Target.CountryName			= Source.CountryName,
	 Target.CountryCode			= Source.CountryCode,
	 Target.Transliteration		= Source.Transliteration,
	 Target.ContinentID			= Source.ContinentID
-- INSERT RECORD
WHEN NOT MATCHED BY Target 
	THEN INSERT(CountryID, 
				LanguageCode, 
				CountryName,
				CountryCode,
				Transliteration,
				ContinentID) 
	VALUES(	Source.CountryID, 
			Source.LanguageCode, 
			Source.CountryName,
			Source.CountryCode,
			Source.Transliteration,
			Source.ContinentID)
-- DELETE RECORD
WHEN NOT MATCHED BY Source THEN DELETE
-- report UPDATE, DELETE and INSERT operations
OUTPUT $action, 
DELETED.CountryID AS TargetCountryID,  
INSERTED.CountryID AS SourceCountryID;
SELECT @@ROWCOUNT;
END
Code example
TRUNCATE TABLE [dbo].[tblDiningDescriptionList]
INSERT INTO [dbo].[tblDiningDescriptionList] (EANHotelID,[LanguageCode],[PropertyDiningDescription])
	SELECT EANHotelID,LanguageCode,PropertyDiningDescription
	 FROM OPENROWSET(BULK 'F:\EAN\DiningDescriptionList.txt',
	                         FORMATFILE='F:\EAN\DiningDescriptionList.xml', FIRSTROW = 2) as BCP

Please enter your comment.