Help Updating Tables From TAB File

Hi All

Im Really New To SQL Server and need to do the folowing - if anyone can help out i will be VERY happy :)

i have a single table in sql server containing products im selling

withing that table is 2 fields that i need to be able to update without changing any of the other fields in that line. (i need to be able to update price and stock field)

there is a field that is unique (field with the barcode)

the data i need to import is from a TAB text file which contains the barcode, stock and price field

hope this makes sense and if anyone can help thats great

cheers

BenHowdy

You can use DTS to import data which will suck data in from the tab file. However, unless you have a bit of experience with SQL its probably worth trying this on some dummy tables to get it right.
You could use DTS to import the tab data into a new table. Then write some code to work your way through the new table to update the existing production table.

If you wanted to update a column based on a unique column value in a table you could use:

Update <tabel_name>
set <column_to_be_updated> = '<some_value>'
where <unique_column> = '<some_value>'

i.e.

update table_product_data
set saleprice = '100'
where barcode = '097364543'

This ensures only the saleprice column in the table is changed where the barcode column = some unique value. That way you can selectively target a row in a particular column based on a value in the barcode column on the same row. Make sure that the barcode column ( or which ever column you use as the primary key for the table ) has unique values, otherwise you may get multiple rows in the saleprice column being updated at the same time.

Post back if probs.

Cheers,

SG.|||thanks for the reply

the problem is i am cool getting it to update 1 record its getting it to run thro a massive tab file and updating about 1500 prices

any suggestions for a thick person ?

cheers

ben|||right i have realised how to do this now - if anyone can give me some example code for in sql to loop thro i have made it import into a new table and i under stand the code you put before but im not sure how to make it loop thro

hope that makes sense!

cheers

ben|||Hi there!

Here I can give you a little example code. Let's say that:
1. "original" - is the table in the database
2. "new" - is the table which has been filled with the data out of the TAB file

update original
set original.stock = new.stock,
original.price = new.price
from original
,new
where original.bar_code_id = new.bar_code_id

Hope that helps you further. If not, post a reply!

Greetings,
Carsten

NOTE: This will not bring any new bar_code_id into "original"! It just updates existing id's|||thats great thanks

will that loop thro each result in the table new ?

thanks for the help

Ben|||you both rule!

its all working now:D:D - you cant believe how happy i am

please both check you private messages

thansk ben

Help to Configure SQL 2005 to listen on a specific Port

I am trying to configure my port for my SQL 2005 named instance. When
I look under the TCP/IP properties dialog box on the IP Address tab
in the see several IP addresses in format IP1,IP2,IP3 and finally
IPALL. Which one am I suppose to configure? When I look at the IP
addresses I don't see the virtual IP address for my SQL Server
Instance. Can anyone help me on this.
I looked at BOL and it says "Right-click each address, and then click
Properties to identify the IP address that you wish to configure." But
right cliking on the address does not give me the properties option.
The server that I am working with is in a Cluster.
ThanksThe multiple IP addresses indicate you have multiple network connections.
You can set a different port for each connection OR you can set the same
port on all connections. I would use the 'ALL' option on a cluster.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> The multiple IP addresses indicate you have multiple network connections.
> You can set a different port for each connection OR you can set the same
> port on all connections. I would use the 'ALL' option on a cluster.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "shub" <shubt...@.gmail.com> wrote in message
> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>
> >I am trying to configure my port for my SQL 2005 named instance. When
> > I look under the TCP/IP properties dialog box on the IP Address tab
> > in the see several IP addresses in format IP1,IP2,IP3 and finally
> > IPALL. Which one am I suppose to configure? When I look at the IP
> > addresses I don't see the virtual IP address for my SQL Server
> > Instance. Can anyone help me on this.
> > I looked at BOL and it says "Right-click each address, and then click
> > Properties to identify the IP address that you wish to configure." But
> > right cliking on the address does not give me the properties option.
> > The server that I am working with is in a Cluster.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for your help. As far as deciding the port number itself do you
follow any guidelines or is there any guidelines by Microsoft?|||For whatever reason, SQL2005 Configuration Manager does NOT list the virtual
server IP address used by the SQL instance. I have no idea why.
But if ListenAll is enabled, you can use Configuration Manager to set TCP
Port under IP All on the Ip Address tab to a port of your choice, and upon
restart the instance the port will be used. Also, you only need to do this on
one of the nodes, and the cluster service will replicate the setting to the
other nodes.
Linchi
"shub" wrote:
> I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||I either use the autoconfig port and lock it in or I pick something that
isn't in use.
I don't recall any guidelines for this from Microsoft.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193279519.381435.155240@.i13g2000prf.googlegroups.com...
> On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> The multiple IP addresses indicate you have multiple network connections.
>> You can set a different port for each connection OR you can set the same
>> port on all connections. I would use the 'ALL' option on a cluster.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>>
>> >I am trying to configure my port for my SQL 2005 named instance. When
>> > I look under the TCP/IP properties dialog box on the IP Address tab
>> > in the see several IP addresses in format IP1,IP2,IP3 and finally
>> > IPALL. Which one am I suppose to configure? When I look at the IP
>> > addresses I don't see the virtual IP address for my SQL Server
>> > Instance. Can anyone help me on this.
>> > I looked at BOL and it says "Right-click each address, and then click
>> > Properties to identify the IP address that you wish to configure." But
>> > right cliking on the address does not give me the properties option.
>> > The server that I am working with is in a Cluster.
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks for your help. As far as deciding the port number itself do you
> follow any guidelines or is there any guidelines by Microsoft?
>