在批量插入期间更改单元格中的值... [英] changing a value in a cell during bulk insert...

问题描述

奇怪的问题，但我想在我的数据库中插入一个csv。表中的一列引用了一个外键。我想知道是否有可能在插入时更改列的值以反映外键的值。



对不起它听起来很复杂，但这里是一个例子......



表：玩家--------------------------- --------表：国家

playerid | playerName | countryID ------------- countryID | countryName



我想插入到玩家表中，而不是countryID ... csv我有国名。是否有可能改变这样的事情...



乔治，英格兰



到



乔治，1



插入？



谢谢你，抱歉愚蠢的问题/短语

解决方案

这里有两张桌子？



表01：

 CREATE TABLE [tbl_MX_players]（
 [playerId] [int]，
 [playerName] [nvarchar]（129），
 [countryID] [int] 
）

表02：

 CREATE TABLE [tbl_MX_country]（
 [countryID] [int]，
 [countryName] [nvarchar]（45）
）

无论如何。将那些.csvtable（s）保存为TAB DELIMITED .txt，用于BULK INSERTION。

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

 INSERT INTO [tbl_MX_country] 
 FROM'c：\ usersrs \\ \\ mx\table02_tdt.txt'

由于这里有一个可能的公共数据点，[countryID]，可以在JOIN语句中使用它来实现在SELECT期间，两个表一起成为一个输出。有关如何在TSQL中加入的详细信息和示例，请参阅BOL。



至于用int替换address...不确定你的意思在那里。



但是要开始，将.csv折腾为.txt并执行CREATE tABLE。一旦数据在数据库中，CREATE TABLE再次使用usg [Idx] [int] IDENTITY（1,1）来添加索引。

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

解决方案

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.

这篇关于在批量插入期间更改单元格中的值...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！