September 29, 2010 at 4:26 pm
Hello,
I have a CSV file that contains e-mail addresses of our existing customer in the database. I would like to use that file to create new records for customers whose e-mail addresses match in the CSV file.
The new record for all customers will be identical, except for the counter which is a unique key:
Here is the structure of the table:
CREATE TABLE [dbo].[Letter_C](
[Counter] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[PrimaryCode_C] [nvarchar](30) NOT NULL,
[UserCreated] [nvarchar](30) NULL,
[UserModified] [nvarchar](30) NULL,
[ContactCode_C] [nvarchar](50) NULL,
[Description_C] [nvarchar](250) NULL,
[LetterName_C] [nvarchar](250) NULL,
[LetterPath_C] [nvarchar](250) NULL,
[LetterCode_C] [nvarchar](50) NULL,
CONSTRAINT [Letter_CIndex] PRIMARY KEY CLUSTERED
(
[Counter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I really appreciate your help on pointing me to the right direction!
Fawad Rashidi
www.fawadafr.com
September 29, 2010 at 5:03 pm
well all you have to do is get the csv file into a table or linked server so you can use a set based operation against it.
i do not see a single column in your table definition which screams "email" to me.
which column holds the email address?
assuming the csv files holds exactly as many columns as your base table, i would create a temp table, bulk insert, and compare the two tables based on the secret email column:
CREATE TABLE #TempContacts(
[Counter] [int] NULL,
EMAIL varchar(200), --added by me!
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[PrimaryCode_C] [nvarchar](30) NOT NULL,
[UserCreated] [nvarchar](30) NULL,
[UserModified] [nvarchar](30) NULL,
[ContactCode_C] [nvarchar](50) NULL,
[Description_C] [nvarchar](250) NULL,
[LetterName_C] [nvarchar](250) NULL,
[LetterPath_C] [nvarchar](250) NULL,
[LetterCode_C] [nvarchar](50) NULL )
BULK INSERT #TempContacts FROM 'c:\Export_o.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)
SELECT EMAIL FROM #TempContacts
EXCEPT
SELECT EMAIL FROM Letter_C
Lowell
September 29, 2010 at 6:13 pm
Thank you Lowell. The e-mail addresses are residing in a different table called CRMContacts. I can easily join the CRMContact with Letter_C by using the ContactCode_C column. In fact, ContactCode in CRMContact table is the primary key.
Questions:
Let's say I have the tempTable ready that contains all the e-mail addresses, how would I create new record in Letter_C table? Currently, the table only contains 5 records that I manually created.
Thank you,
Fawad Rashidi
www.fawadafr.com
September 29, 2010 at 7:21 pm
it's actually pretty easy;
INSERT INTO Letter_C (list of columns)
SELECT (same list of columns,same order) FROM #Temp
EXCEPT
SELECT same list of columns,same order FROM Letter_C
this will find all the records that do not exist in Letter_C, so you can insert them.
comment out the INSERT command to see the records that would be inserted.
Lowell
September 30, 2010 at 1:11 pm
Hello Lowell,
Thank for your help! I created a temp table called dbo.September and imported all the e-mail addresses. Here is the structure of the table:
CREATE TABLE [dbo].[September](
[nvarchar](50) NOT NULL
) ON [PRIMARY]
My second table dbo.CRMContact has the following structure and contains all the e-mail addresses, name, address, etc.
CREATE TABLE [dbo].[CRMContact](
[Counter] [int] IDENTITY(1,1) NOT NULL,
[ContactCode] [nvarchar](30) NOT NULL,
[EntityCode] [nvarchar](30) NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[ContactSalutationCode] [nvarchar](30) NULL,
[ContactFirstName] [nvarchar](50) NULL,
[ContactMiddleName] [nvarchar](50) NULL,
[ContactLastName] [nvarchar](50) NULL,
[ContactSuffixCode] [nvarchar](30) NULL,
[BusinessTitle] [nvarchar](30) NULL,
[JobRoleCode] [nvarchar](30) NULL,
[DepartmentCode] [nvarchar](30) NULL,
[AssignedTo] [nvarchar](30) NULL,
[Address] [nvarchar](200) NULL,
[Country] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](5) NULL,
[PostalCode] [nvarchar](30) NULL,
[County] [nvarchar](30) NULL,
[Email1] [nvarchar](50) NULL,
CONSTRAINT [PK_CRMContact] PRIMARY KEY CLUSTERED
(
[ContactCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I can join both dbo.CRMContact with dbo.September on email addresses since both tables have this field available.
For every e-mail address that is matching, I would like to create a new record in the third table dbo.Letter_C. Here is the structure of the third table:
CREATE TABLE [dbo].[Letter_C](
[Counter] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[PrimaryCode_C] [nvarchar](30) NOT NULL,
[UserCreated] [nvarchar](30) NULL,
[UserModified] [nvarchar](30) NULL,
[ContactCode_C] [nvarchar](50) NULL,
[Description_C] [nvarchar](250) NULL,
[LetterName_C] [nvarchar](250) NULL,
[LetterPath_C] [nvarchar](250) NULL,
[LetterCode_C] [nvarchar](50) NULL,
CONSTRAINT [Letter_CIndex] PRIMARY KEY CLUSTERED
(
[Counter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Attached Excel document contains sample data I created manually.
I followed your instruction but was unable to create BULK INSERT. I truly appreciate your help on this!
Fawad Rashidi
www.fawadafr.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply