changing a value in a cell during bulk insert...

Question

0.00/5 (No votes)

See more:

oddball question, but i'd like to insert a csv into my database. one of the columns in the table references a foreign key. i was wondering if it were possible to change the value of the the column upon insert to reflect the value of the foreign key.

sorry it sounds convoluted, but here's an example...

table: players----------------------------------- table: country
playerid | playerName | countryID ------------- countryID | countryName

i'd like to insert into the players table, but rather than countryID...the csv i have has country names instead. is it possible to change something like this...

george, england

to

george, 1

upon insert?

thank you and sorry for the goofy question/phrase

Posted 14-Aug-13 16:49pm

memberxxxxxxxxxxxxxxxxx

Updated 14-Aug-13 21:07pm

v2

Add a Solution

Comments

RedDk 15-Aug-13 12:58pm

Not two tables ...

Ok, then I don't have any idea what [nvarchar] to [int] might provide as advantage after conversion. Inform me.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2013-08-14T18:00:00

Two tables here?

Table01:

CREATE TABLE [tbl_MX_players](
	[playerId][int], 
		[playerName][nvarchar](129), 
			[countryID][int]
	)

Table02:

CREATE TABLE [tbl_MX_country](
	[countryID][int],
		[countryName][nvarchar](45)
	)

Anyway. Save those/that .csv "table(s)" as TAB DELIMITED .txt for use in BULK INSERTION.

INSERT INTO  [tbl_MX_players]
	FROM 'c:\users\mx\table01_tdt.txt'

INSERT INTO [tbl_MX_country]
	FROM 'c:\users\mx\table02_tdt.txt'

Since you've got a potentially common data point here, [countryID], that could be used in a JOIN statement to bring the two tables together into one output during a SELECT. See the BOL for details and sample examples of how to JOIN in TSQL.

As for substitution of "address" with "int" ... not sure what you mean to do there.

But to start, toss the .csv for .txt and do the CREATE tABLE. Once data's in the database, CREATE TABLE once more usg [Idx][int]IDENTITY(1,1) to add an index.