Linking an SQL INSERT statement to a .csv file containing data for a VALUE

Question

Post reply

Linking an SQL INSERT statement to a .csv file containing data for a VALUE

jonclay_london

SSC Journeyman

Points: 91
More actions
November 29, 2007 at 5:08 am

#179576

Hello
I am using the following SQL statement to update the records in our database with a website address.
However, doing it this way would mean that I have to insert a new address_number each time that I want to add the website to a new record, and then run the query again.
I have managed to output a .csv file containg the address_number records, and I was wondering if I could amend my script to read this .csv file and insert the website address into every record where the address_number is shown in the .csv file?
Many thanks for your help.
Jon
declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)
insert into [communications]
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by,
amended_on, cli_number, communication_number)
VALUES (127, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

Hi Jon,

Not sure I folllow what you are trying to do.

If you need to load a csv I would use DTS or SSIS.

Allen

jonclay_london SSC Journeyman Points: 91 More actions · Answer 2

Hi Allen

Thanks for your reply. I'm not 100% sure that I need to use a .csv file but thought it might be one way around my problem.

I am trying to achieve this.... The first VALUE (i.e. address_number = 127) is just one of many address_number VALUES that I need to INSERT the website address for. So, the next address_number might be 128 then 129 then 130 and so on. I don't want to have to run the script x number of times and change the address_number each time to get what I want.

Is there a way in SQL to say update all address_numbers from the communications table with the other values shown in the statement (i.e. the website address etc), where the address_number is ('128','129', '130') and so on?

Thanks

Jon

allen davidson SSCertifiable Points: 7042 More actions · Answer 3

Is value of the address_number column unique on the table?

jonclay_london SSC Journeyman Points: 91 More actions · Answer 4

Yes, each address has a different address_number so it is unique to that particular record.

Thanks,

Jon

allen davidson SSCertifiable Points: 7042 More actions · Answer 5

This sounds like the address_number is your primary key on the table. Using an IDENTITY column would do away with the neeed for you to update the column manually.

jonclay_london SSC Journeyman Points: 91 More actions · Answer 6

Thanks Allen.

If this means that I have to insert a column into the database then I won't be able to do this. We receive frequent upgrades from the database supplier and any changes to the original structure of the tables/columns causes problems.

Is there any other way to get around this issue do you think?

Thanks for all your help so far.

Jon

allen davidson SSCertifiable Points: 7042 More actions · Answer 7

Is there a way in SQL to say update all address_numbers from the communications table with the other values shown in the statement (i.e. the website address etc), where the address_number is ('128','129', '130') and so on?

I just know I'm not understanding the problem but to answer your question above...

Air code

UPDATE

communications

SET

MyField = @MyValue...

WHERE

address_number IN (101,102,103)

jonclay_london SSC Journeyman Points: 91 More actions · Answer 8

Thanks Allen. I'm sorry if I'm explaining things really badly. I'll just try once more (maybe it's not even possible in SQL, I'm not sure).

To do what I want to do I would have to do e.g.

declare @ComNum int

set @ComNum = (select max(communication_number)+1 from communications)

insert into [communications]

(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by,

amended_on, cli_number, communication_number)

VALUES (127, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);

then clear the query and do...

declare @ComNum int

set @ComNum = (select max(communication_number)+1 from communications)

insert into [communications]

(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by,

amended_on, cli_number, communication_number)

VALUES (128, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);

then clear the query and do...

declare @ComNum int

set @ComNum = (select max(communication_number)+1 from communications)

insert into [communications]

(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by,

amended_on, cli_number, communication_number)

VALUES (129, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum);

.... so basically the address_number VALUE changes each time.

What I want to do is get a script that avoids me having to run the above script 400 or so times with each different address_number number... if you get my drift!

Thanks so much and apologies once again!

Jon

allen davidson SSCertifiable Points: 7042 More actions · Answer 9

Ok I'm following slowly. Where do the values other than address_number come from. Please tell me they are not hard coded into your script!

jonclay_london SSC Journeyman Points: 91 More actions · Answer 10

Thanks. The other values are the same for every record so I've done a select * for the communications table and copied over the values for the other records that are already in there - only the address_number needs to change. Is this what you mean?

allen davidson SSCertifiable Points: 7042 More actions · Answer 11

So the address_number changes _and_ the communication_number (otherwise we would not be having this discussion).

How about

SELECT TOP 400

IDENTITY(INT,1,1) AS Number

INTO

#Number

FROM

master.dbo.SysColumns sc1 CROSS JOIN master.dbo.SysColumns sc2 -- 12 million +

CREATE TABLE

communications -- simplified

(address_number INT NOT NULL,

notes VARCHAR(255) NOT NULL,

communications_number INT NOT NULL)

INSERT INTO

communications

(address_number,

-- other columns

notes,

communications_number)

SELECT

n1.Number,

-- other values

'www.abc.co.uk',

n1.Number + 200 -- 200 is an offset make it whatever you just to get this no different to address_number

FROM

#Number n1 WHERE n1.Number <= 100 -- however many you want

jonclay_london SSC Journeyman Points: 91 More actions · Answer 12

Hi Allen

Thanks for all your help. I'm due in a meeting now but will try the script tomorrow.

Cheers and best wishes,

Jon