INSERT INTO Help - 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

INSERT INTO Help

Author

Topic

bill79
Starting Member

13 Posts

Posted - 2010-06-20 : 16:14:31

Hi all,

I need to amend two columns in one table for many ProductCodes. As such I would really appreciate some assistance in how best to achieve this.

I can extract the data from the database with the following query for 1 product code:

SELECT * FROM [DB TABLE NAME] WHERE ProductCode = "number"

Now my thoughts were to extract all the poduct codes into Excel, manipulate the data and load it all back in. So I guess the only way to do this is to add all the 2,000 productCodes to the end of the WHERE statement as per the following:

SELECT * FROM [DB TABLE NAME] WHERE ProductCode = "number1, number2, number3, number4, number5, number4000]

Now once I have all this data I'm going to amend the columns and then I want to insert it all back in; therefore how would I do this with such large amounts of data?

Any help on achievening this and simplyfying the process would be much appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-20 : 17:41:20

What kind of manipulation do you want to do? It's conceivable that you can do the manipulation in SQL and avoid all the export/import complexity.

--
Gail Shaw
SQL Server MVP

bill79
Starting Member

13 Posts

Posted - 2010-06-20 : 19:17:32

Hi,

Well I need to amend the text that lies within two columns. The text that I have to put in there is unique for each ProductCode; therefore I don't forsee how I can do this in SQL without the aid of Excel, which makes amending the records much easier.

Can you tell me if my query is correct for getting all the ProductCode data and can you tell me the best way to import the data back into SQL based on the fact that I have thousands of ProductCodes and their associated columns?

Thanks

bill79
Starting Member

13 Posts

Posted - 2010-06-20 : 19:51:21

Hi,

Looks like OPENDATASOURCE might do it for me; therefore I have used Excel to connect to the DB Table I want to edit and I have retrieved all records.

So, I will amend the necessary tables and save the Excel spreadsheet to desktop.

Would the following therefore be correct to load the data back in:

SELECT * INTO [DB TABLE NAME] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\mydesktop\myspreadsheet.xls;Extended Properties=Excel 8.0')...[spreadhseetname$]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-21 : 02:13:47

If this is a one-time deal and you already have the data you want in excel, it's much easier to create the insert/update statements directly in excel like this:

But what makes it so difficult to just do it in SQL directly? There are some pretty smart people here and you can do some rather fancy stuff with sql..

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

bill79
Starting Member

13 Posts

Posted - 2010-06-22 : 09:15:38

Hi,

Yes, I need only do this once; therefore I have extracted the table and column directly from the database into Excel. I have amended the data that I needed to in the various cells; therefore I'm ready to put it back in.

Can you possibly tell me how I do this please?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-22 : 09:40:09

Did you see my previous post? How much data are we talking about? If creating sql queries in your excel sheet is impractical, I'd either use the import/export wizard in Management Studio to do it directly from excel or save the excel file as csv and do it using bcp instead. By saving an excel-file as csv it will be tab-separated and importing a tab-separated file using bcp should be really easy -> http://msdn.microsoft.com/en-us/library/ms162802.aspx

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

bill79
Starting Member

13 Posts

Posted - 2010-06-22 : 10:51:24

Hi,

There are 50,000 plus rows.

I don't have Management Studio and I'm using an Open Source DB Tool (Toad) which on the free version does not allow export.

So, as you have suggested DCP seems the best way forward.

Can you help me with the SQL code to do this please as I'm not familiar enough to do it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-22 : 11:19:01

Well there a lots and lots of ways to do this. But, Gail asked what you want to do. If you can answer WHAT you want to do we might be able to suggest HOW to do it.

Maybe this link will help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bill79
Starting Member

13 Posts

Posted - 2010-06-22 : 11:21:42

Hi, I want to INSERT the table and all the columns back into the database.

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-22 : 14:27:36

I don't have Management Studio and I'm using an Open Source DB Tool (Toad) which on the free version does not allow export.

you can download SQL Management Studio from microsoft for free

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-22 : 14:30:02

is there some reason you cant do a join on the table for the update/insert on the product id

bill79
Starting Member

13 Posts

Posted - 2010-06-23 : 11:48:36

Hi,

Going to use DTS import; however, I have a question.

In excel I have connected to the specific table that I want to edit. I have made the amends and saved this to desktop.

Now I see that I can right click the database in Express Management Studio 2008 and then select import. Import seems straight forward; however, it doesn't appear to allow you to define the table that I want to amend? Or allow me to map the spreadsheet to the specifc table.

Anyone possibly help me out?

bill79
Starting Member

13 Posts

Posted - 2010-06-23 : 13:09:36

DTS is erroring even on export.

Seems that may not be the best method after all.

Can someone help me with getting the spreadsheet data back into SQL using DCP?

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 13:15:24

yeah

Use bcp

Who installed the database for you? See if the have the media

DTS? I thought you didn't have Manglement Studio or Enterprise Mangler?

What version of SQL is this?

and what is this?

SELECT * FROM [DB TABLE NAME] WHERE ProductCode = "number1, number2, number3, number4, number5, number4000"]

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

bill79
Starting Member

13 Posts

Posted - 2010-06-23 : 16:29:49

Hi,

Yes installed free Express version of 2008.

It sql server 2008 and I have no involvement with it.

That was just a query I was running.

Anyhow I have my excel out put of the table, I have made my changes and now need to get it back into the DB.

Any assistance on doing this would be appreciated as I'm struggling and getting nowhere fast at the moment.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-24 : 10:44:45

It seems you are going about this in a very unorthodox way. But, if you have Management Studio use the object browser to browse to your database. Then right-click on the database and select Tasks -> Import Data. That should launch the import wizard. On the Choose a Data Source screen change the Data Source to Excel and then just follow the rest of the wizard.

bill79
Starting Member

13 Posts

Posted - 2010-06-24 : 12:11:22

Hi,

Okay, thanks for that. A few questions:

The database has many tables within it. I exported the table I wanted to edit into Excel adn saved this to desktop as 'Book1'. I have been through the Wizard up to 'Run' and at no time has it mentioned this spredhseet being matched to that specifc table.

Based on that do I need to export the whole of the database into Excel and work with that then import back in using this method?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-24 : 12:47:38

One of the "screens" should be entitled: Select Source Tables and Views. That will allow you to select the Excel Sheet to use and the Destination the table you want that data to go to (drop down). After you select the destination table the Edit Mappings button should be enabled. That will allow you to map the columns from the sheet to the table.

bill79
Starting Member

13 Posts

Posted - 2010-06-25 : 06:48:58

Will try that and let you know how I get on - really appreciate your help on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources