Importing using a query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Importing using a query

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-14 : 04:54:37

I can use this query to update certian fields from one table to another.

UPDATE wce_contact
SET pagerank = wce_import.pagerank
FROM wce_contact INNER JOIN wce_nw AS wce_import ON wce_contact.WebSite = wce_nw.website

What query would I need to use that would import data from the table 'wce_nw' into 'wce_contact'

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 04:56:37

insert wce_nw (col1,col2,col3)
select col1,col2,col3
from wce_contact

No, you're never too old to Yak'n'Roll if you're too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-14 : 05:03:29

I get this error using this qry

insert wce_nw (website, expressemail, salutation, website1, website2, website3, blank)
select website, expressemail, salutation, website1, website2, website3, blank
from wce_contact

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

JT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 05:05:49

that means the length of a column in wce_nw is shorter than that of wce_contact

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-14 : 05:05:54

you try to insert a string with more characters than the column can maximal accommodate.

-------------------------
R..

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-14 : 05:23:26

I have fixed the character lengths of a few fields and have run the query successfully.

However I think the query that I have used inserted all the data from wce_contact into wce_nw. I wanted it to be the other way round, so wce_nw gets imported into wce_contact. Should I just change the query to look like this...I want this to be an append query

insert wce_contact (website, expressemail, salutation, website1, website2, website3, blank)
select website, expressemail, salutation, website1, website2, website3, blank
from wce_nw

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 05:29:49

For a first run it is right.
If you want to append you have to make sure not to insert data that has already been inserted in a run before...
So what are the columns to check for unwanted data because they are already in destination table?

No, you're never too old to Yak'n'Roll if you're too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-14 : 05:38:34

The main column I want to check for 'already been imported' is website.

So an insert query isn't the same as append?

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 05:42:38

insert wce_contact (website, expressemail, salutation, website1, website2, website3, blank)
select website, expressemail, salutation, website1, website2, website3, blank
from wce_nw as nw
where not exists (select * from wce_contact c where c.website = nw.website)

But maybe I misunderstood!
If you mean an append with no matter of duplicate entries you can do your statement like shown above.

No, you're never too old to Yak'n'Roll if you're too young to die.

Topaz
Posting Yak Master

199 Posts

Posted - 2009-07-14 : 05:54:21

Just to confirm...

my wce_nw table only has a few thousand contacts in it. My wce_contact table has over 1 million.

I don't want to run this query and 'destroy/ruin' my wce_contact table. Can you assure me that it will simply add the contacts from wce_nw to wce_Contact whilst checking for duplicate values. (my databse is precious to me)

JT

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 06:04:16

I will NEVER assure anything!
The success depends on:
given (detailed) information
- table structure
- sample data
- wanted output / result

I can only assume that I am understanding what you want.
I cannot see the data which would be processed in the real world.

My query will work fine in relation to the given information.

You have the data and you have to do your tests!

Greetings
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources