How do you update existing rows with an import? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

How do you update existing rows with an import?

Author

Topic

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 11:47:18

Two columns, 1000 rows,

Column 1 - Username

Column 2 - Title

1000 Rows with data in the Username column, but empty 1000 rows for title.

I want to import into the existing empty rows for 1-1000 in the Title column without starting the data at row 1001 and so on.

Can this be done?

I have tried a couple of utilities but it only adds new rows, not update. (SQLyog)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-01 : 12:43:51

You can't import into the existing rows, but you can import the titles into a table and use that to update the existing empty rows, if you can link them uniquely to your table. Something like
UPDATE tgt
SET Title = srcTitle
FROM myTable tgt
INNER JOIN
sourceTable src
ON
tgt.? = src.?

It sounds like your table has an identity column, and that may be useful

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 12:48:38

Most of us use staging tables anyway (unless it's a full file replacement) to load data.

This gives us the ability the audit the data as well a build processes to, nomalize, distribute modify, etc with T-SQL...WAIT THEY CALL THAT ETL now!!!! I am Soooo behind the times....I just don't have the ABILITY to make up new buzzwrods for things we have been doing...for...for...EONS...but I digress

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 13:25:12

Can someone give me an example of that INNER JOIN?

Here is my tables:

Source table name - threads
Source column - username

Destination table name - csvimporttest
Destination column - namez

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-01 : 13:55:36

I thought I did. Does username link to namez and does threads have the title info in it? If there's no link,then it'd be faster for you to update the 1000 rows manually.

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 13:55:36

assuming UserName and Namez are the keys?

SELECT * FROM threads a JOIN csvimporttest b ON a.UserName = b.NameZ

????

What if there is an existing value in Title already?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 13:58:13

There are no keys for these columns, should I create a foreign key that links the two?

Right now they have no link at all, just two columns in two tables..

I apologize for the inexperienced questions, I am a junior DBA in training =)

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 14:49:08

threads.username has values

csvimporttest.namez does not

i want to move the values in threads.username into csvimporttest.namez and update the rows that are there, not add on rows

i dont think there can be keys set up since the values dont match?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-01 : 15:22:20

This would be a lot simpler if you gave us the complete data structure for each table, sample data for each table, and your desired end result for your query. ( I wasn't kidding when I said it migtht just be faster to do it manually!)

Jim

Everyday I learn something that somebody else already knew

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 15:24:14

Well, its over a million rows, lol. Manual is no option.

Ok, both tables are varchar(250), not null

They are forum titles, actually, such as the title of this forum, "How do you update existing rows with an import?"

I am moving a forum from mssql to mysql, I exported all the rows from mssql into a txt file, and I also have them imported into a test column on a test table.

What else would you like me to provide? Thanks for replying

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-01 : 15:56:47

Please give us the complete data structure for each table, sample data for each table, and your desired end result for your query.

Jim

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Everyday I learn something that somebody else already knew

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 16:22:40

SOURCE TABLE:

table name - datafrom

fieldname | datatype | len | default | not null?
titles | varchar | 250 | '' | Y
sourcenames | varchar | 250 | '' | Y

DESTINATION TABLE:

table name - datato

fieldname | datatype | len | default | not null?
titles | varchar | 250 | '' | Y
names | varchar | 250 | '' | Y

VALUES in datafrom

titles | sourcenames
Admin | Mike
Supervisor | Cory
User | Danny

VALUES in datato

titles | sourcenames
| Mike
| Cory
| Danny

I want to move "titles" data from "datafrom" into "titles" in "datato", starting with the same row the user "Mike" is on and so on.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 16:54:51

[code]UPDATE a
SET a.titles = b.titles
FROM datato a INNER JOIN datafrom b on a.sourcenames = b.sourcenames[/code]
NOTE: Run it in some test environment and check if it produces your expected results.

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 16:57:57

quote:
Originally posted by vijayisonly

UPDATE a
SET a.titles = b.titles
FROM datato a INNER JOIN datafrom b on a.sourcenames = b.sourcenames

NOTE: Run it in some test environment and check if it produces your expected results.

Query : UPDATE a SET a.titles = b.titles FROM datato a INNER JOIN datafrom b ON a.sourcenames = b.sourcenames
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM datato a INNER JOIN datafrom b ON a.sourcenames = b.sourcenames' at line 3
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 17:04:07

Oops..Sorry...I do not know the syntax for MySQL. This is a dedicated MS SQL Server Forum. You can get better help in a MySQL forum.

noonz
Starting Member

33 Posts

Posted - 2010-07-01 : 17:13:22

Yeah, but I am moving this from MSSQL

This data was exported from MSSQL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-01 : 18:06:09

Move all your data, as is, from mysql database into sql server database and then try the query.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources