Create new record using external CSV file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create new record using external CSV file

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2010-09-29 : 18:19:09

Hello,

I have a CSV file that contains e-mail addresses. I would like to use that file to create new records for customers whose e-mail addresses exist 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]

Could you please provide me your help on this?

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 18:22:28

Could you post a sample of the csv file?

You could just bcp the data into a staging table (one that matches the layout of the file), and then use T-SQL INSERT/SELECT to move the data from the staging table into Letter_C.

Alternatively, you can use SSIS/DTS,import/export wizard, OPENQUERY. I prefer bcp though as it's super fast and easy.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

fawadafr
Starting Member

47 Posts

Posted - 2010-09-30 : 15:32:18

Hello Tara,

Thank you very much for the reply. I was actually able to create a temp table (dbo.September) and import all my e-mail addresses. Here is the structure of dbo.September:


CREATE TABLE [dbo].[September](
[email] [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. of all my customers:


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]

Here is a sample data in txt format:


Counter,DateCreated,DateModified,PrimaryCode_C,UserCreated,UserModified,ContactCode_C,Description_C,LetterName_C,LetterPath_C,LetterCode_C
6,9/29/10 12:00 AM,9/29/10 12:00 AM,,arashidi,arashidi,CCTC-000002,Sent on 09/24/2010 to everyone.,September 2010 Newsletter.pdf,,CRMDOC-002916
7,9/29/10 12:00 AM,9/29/10 12:00 AM,,arashidi,arashidi,CCTC-000003,Sent on 09/24/2010 to everyone.,September 2010 Newsletter.pdf,,CRMDOC-002916

How can I write the BULK INSERT statement to insert new records in dbo.Letter_C for e-mail address matches dbo.September and dbo.CRMContact?

Thank you,

--
Fawad Rashidi
Web Developer
www.fawadafr.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 15:34:44

If your data has already been imported into a table, then we don't need BULK INSERT or any other import tool. We just need T-SQL now.

INSERT INTO Letter_C (...)
SELECT ...
FROM CRMContact c
JOIN September s
ON c.Email1 = s.email

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

fawadafr
Starting Member

47 Posts

Posted - 2010-10-04 : 14:42:24

Dear Tara,

Thank you very much for your help. I really appreciate it. I was able to create the new records using INSERT INTO statement.

--
Fawad Rashidi
Web Developer
www.fawadafr.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-04 : 14:43:03

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources