Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
>
> the
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>sql
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Wednesday, March 28, 2012
LOOPING UPDATE
Hi
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>
I just require a bit of guidance on a SQL query I am writing. I am updating
a table with values, and the first field is a TYPE field, I set this to A
and then populate fields 2-15 with a variety of default updates and values
from other tables, I then do a second insert whreeby I set the same TYPE
field to B and update fields 2-5 and then the unique fields 16-20.
At the moment my second update has two issues;
1. It sets EVERY type to B (although it correctly doubles the amount of
entries in the table)
2. The B entries are appended to the bottom of the table, ideally I want the
table structure to be ABABABAB etc
Any help to a SQL newbie appreciated!> 1. It sets EVERY type to B (although it correctly doubles the amount of
> entries in the table)
Sounds like your WHERE clause may be at fault. Could you post a CREATE TABLE
statement and the UPDATE/INSERT statements so that we can reproduce your
problem.
> 2. The B entries are appended to the bottom of the table, ideally I want
the
> table structure to be ABABABAB etc
Tables have no logical ordering. If you want to see the results in a
particular order then use ORDER BY on a SELECT statement when you query the
table. A clustered index orders data in physical storage but not necessarily
when you query the table.
--
David Portas
SQL Server MVP
--|||Hi David
I have rewritten the part of the routine with a separate WHERE clause on the
type field at the end of the update and this seems to work so thank you.
I have several more loops to write, I will then try the Order by at the end
of the routine.
Thank you very very much for your quick and informative response, it really
is appreciated.
Steve
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8ZKdncKsYuPgDrHdRVn-uA@.giganews.com...
> > 1. It sets EVERY type to B (although it correctly doubles the amount of
> > entries in the table)
> Sounds like your WHERE clause may be at fault. Could you post a CREATE
TABLE
> statement and the UPDATE/INSERT statements so that we can reproduce your
> problem.
> > 2. The B entries are appended to the bottom of the table, ideally I want
> the
> > table structure to be ABABABAB etc
> Tables have no logical ordering. If you want to see the results in a
> particular order then use ORDER BY on a SELECT statement when you query
the
> table. A clustered index orders data in physical storage but not
necessarily
> when you query the table.
> --
> David Portas
> SQL Server MVP
> --
>
Monday, March 26, 2012
looping through query result column and PRINT to log file....
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file...
Thanks in advanceI got it :)
For any1 who might be interested heres how its done
DECLARE @.NbrList VarChar(300)
SELECT @.NbrList = COALESCE(@.NbrList + ', ', '') + CAST(Invoice AS varchar(30))
from TRANSACTIONS where ProductID = 'CVSDBN'
PRINT @.NbrList
Thanks ;)sql
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file...
Thanks in advanceI got it :)
For any1 who might be interested heres how its done
DECLARE @.NbrList VarChar(300)
SELECT @.NbrList = COALESCE(@.NbrList + ', ', '') + CAST(Invoice AS varchar(30))
from TRANSACTIONS where ProductID = 'CVSDBN'
PRINT @.NbrList
Thanks ;)sql
looping record by record and processing
I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with thisUse a trigger...
You'll probably have to do an initial clean up first...|||Please brett can you show me how to do this in code...i have figured out i will need a triger but i have been trying all kinds of stuff and not getting anywhere code wise...
You'll probably have to do an initial clean up first...|||Please brett can you show me how to do this in code...i have figured out i will need a triger but i have been trying all kinds of stuff and not getting anywhere code wise...
Friday, March 23, 2012
Loop Update
I'm trying to find a way to loop through the testnewsRecipients table and
insert the value of the USERID field equal to the USERID field in the
tempUsers table. As you can see in my DDL, currently the USERID field in
testnewsRecipients table is empty and the only value the 2 tables have in
common is the email field.
Can this be done with a loop statement?
DDL ****************
CREATE TABLE testUsers(
userID int NULL,
userEmail varchar(50) NULL
) ON [PRIMARY]
GO
insert into testUsers (userID, userEmail) values
('101', 'test1@.test.com')
insert into testUsers (userID, userEmail) values
('102', 'test2@.test.com')
insert into testUsers (userID, userEmail) values
('103', 'test3@.test.com')
insert into testUsers (userID, userEmail) values
('104', 'test4@.test.com')
GO
CREATE TABLE testnewsRecipients(
recipID int IDENTITY(1,1) NOT NULL,
userID int NULL,
recipEmail varchar(100) NULL
) ON [PRIMARY]
--
insert into testnewsRecipients (userID, recipEmail) values
('', 'test1@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test2@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test3@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test4@.test.com')UPDATE testnewsRecipients
SET userID = t2.userID
FROM testnewsRecipients t1
INNER JOIN testUsers t2
ON t2.userEmail = t1.recipEmail
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> I'm trying to find a way to loop through the testnewsRecipients table and
> insert the value of the USERID field equal to the USERID field in the
> tempUsers table. As you can see in my DDL, currently the USERID field in
> testnewsRecipients table is empty and the only value the 2 tables have in
> common is the email field.
> Can this be done with a loop statement?
>
> DDL ****************
> CREATE TABLE testUsers(
> userID int NULL,
> userEmail varchar(50) NULL
> ) ON [PRIMARY]
> GO
> insert into testUsers (userID, userEmail) values
> ('101', 'test1@.test.com')
> insert into testUsers (userID, userEmail) values
> ('102', 'test2@.test.com')
> insert into testUsers (userID, userEmail) values
> ('103', 'test3@.test.com')
> insert into testUsers (userID, userEmail) values
> ('104', 'test4@.test.com')
> GO
> CREATE TABLE testnewsRecipients(
> recipID int IDENTITY(1,1) NOT NULL,
> userID int NULL,
> recipEmail varchar(100) NULL
> ) ON [PRIMARY]
> --
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test1@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test2@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test3@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test4@.test.com')
>
>|||Thanks, but I was hoping someone would provide a "FOR" loop example for
educational purpose. I've never done a loop with sql and wanted to learn.
would a loop work on my example?
"Mike C#" <xxx@.yyy.com> wrote in message news:p%vag.3964$Xa5.673@.fe11.lga...
> UPDATE testnewsRecipients
> SET userID = t2.userID
> FROM testnewsRecipients t1
> INNER JOIN testUsers t2
> ON t2.userEmail = t1.recipEmail
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
>|||Yeah with a CURSOR or a WHILE statement and a counter variable. Lot more
work than doing it with a single UPDATE statement however. Look up DECLARE
CURSOR (ugh) and WHILE in BOL.
"scott" <sbailey@.mileslumber.com> wrote in message
news:OLekAEWeGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Thanks, but I was hoping someone would provide a "FOR" loop example for
> educational purpose. I've never done a loop with sql and wanted to learn.
> would a loop work on my example?
>
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:p%vag.3964$Xa5.673@.fe11.lga...
>
insert the value of the USERID field equal to the USERID field in the
tempUsers table. As you can see in my DDL, currently the USERID field in
testnewsRecipients table is empty and the only value the 2 tables have in
common is the email field.
Can this be done with a loop statement?
DDL ****************
CREATE TABLE testUsers(
userID int NULL,
userEmail varchar(50) NULL
) ON [PRIMARY]
GO
insert into testUsers (userID, userEmail) values
('101', 'test1@.test.com')
insert into testUsers (userID, userEmail) values
('102', 'test2@.test.com')
insert into testUsers (userID, userEmail) values
('103', 'test3@.test.com')
insert into testUsers (userID, userEmail) values
('104', 'test4@.test.com')
GO
CREATE TABLE testnewsRecipients(
recipID int IDENTITY(1,1) NOT NULL,
userID int NULL,
recipEmail varchar(100) NULL
) ON [PRIMARY]
--
insert into testnewsRecipients (userID, recipEmail) values
('', 'test1@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test2@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test3@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test4@.test.com')UPDATE testnewsRecipients
SET userID = t2.userID
FROM testnewsRecipients t1
INNER JOIN testUsers t2
ON t2.userEmail = t1.recipEmail
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> I'm trying to find a way to loop through the testnewsRecipients table and
> insert the value of the USERID field equal to the USERID field in the
> tempUsers table. As you can see in my DDL, currently the USERID field in
> testnewsRecipients table is empty and the only value the 2 tables have in
> common is the email field.
> Can this be done with a loop statement?
>
> DDL ****************
> CREATE TABLE testUsers(
> userID int NULL,
> userEmail varchar(50) NULL
> ) ON [PRIMARY]
> GO
> insert into testUsers (userID, userEmail) values
> ('101', 'test1@.test.com')
> insert into testUsers (userID, userEmail) values
> ('102', 'test2@.test.com')
> insert into testUsers (userID, userEmail) values
> ('103', 'test3@.test.com')
> insert into testUsers (userID, userEmail) values
> ('104', 'test4@.test.com')
> GO
> CREATE TABLE testnewsRecipients(
> recipID int IDENTITY(1,1) NOT NULL,
> userID int NULL,
> recipEmail varchar(100) NULL
> ) ON [PRIMARY]
> --
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test1@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test2@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test3@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test4@.test.com')
>
>|||Thanks, but I was hoping someone would provide a "FOR" loop example for
educational purpose. I've never done a loop with sql and wanted to learn.
would a loop work on my example?
"Mike C#" <xxx@.yyy.com> wrote in message news:p%vag.3964$Xa5.673@.fe11.lga...
> UPDATE testnewsRecipients
> SET userID = t2.userID
> FROM testnewsRecipients t1
> INNER JOIN testUsers t2
> ON t2.userEmail = t1.recipEmail
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
>|||Yeah with a CURSOR or a WHILE statement and a counter variable. Lot more
work than doing it with a single UPDATE statement however. Look up DECLARE
CURSOR (ugh) and WHILE in BOL.
"scott" <sbailey@.mileslumber.com> wrote in message
news:OLekAEWeGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Thanks, but I was hoping someone would provide a "FOR" loop example for
> educational purpose. I've never done a loop with sql and wanted to learn.
> would a loop work on my example?
>
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:p%vag.3964$Xa5.673@.fe11.lga...
>
Loop through tables names to use with update
Hi,
I have a group of tables that need to be updated with all the same colums name. I want to make a SP then pass the name of the table to it. Is this possiable? if so how do I do this
Thanks
Hican you try this
sp_msForEachTable @.command1='Update ? set colname = value'
you can also loop thu a cursor against this quey
select Table_Name from Information_Schema.Tables
|||
Please do not use the undocumented stored procedures. They are meant for internal use and any dependency that you take on undocumented SPs/functions is risky for your code. It can be broken anytime due to a change whether it is a service pack or new release. We do not make any guarantees on the interface/behavior of the undocumented SPs.
For this particular problem, you can use a cursor loop using INFORMATION_SCHEMA.TABLES or sys.tables views.
|||That is not the question, the question is how to write a loop that loops through tables, updating their row-definitions. Not just to display table-namessqlMonday, March 12, 2012
Lookup table insert, update, and delete...
All,
Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.
We can certainly write this but before reinventing the wheel I figure I'd ask and see.
Many Thanks,
IsaacThe DB Explorer works extensively with look-ups. You will have to define the relationship by referential constraints in the database, or as navigation paths in the client front-end.
You find all resources at MayeticVillage (http://www.MayeticVillage.com/DB-Explorer)
I'll be glad to answer all your questions about it.|||You can edit table data directly in Enterprise Manager.|||You can even use the query analyzer to maintain your data by DML statements, but I invite you to experience the ease of working with the DB Explorer.|||Gosh, I had no idea that DB Explorer was "shareware".
And all this time I've been going to the trouble of right-clicking on any table in Enterprise Manager and selecting "Open Table/Return All Rows"...|||The idea behind the DB Explorer is to exploit the essence of the relational model: the relationship between entities. To work with single tables / views is simpel, any tool including the Enterprise Manager can do so. To put a table / view into a relational context is normally the exclusive domain of client application development, but the DB Explorer is able to generate a user interface on-the-fly, including lookups.
Moreover, you can instantly use the relationships to navigate through your data: from a customer record to the related order records, or vise versa from an order to the corresponding customer. Even within the Enterprise Manager with its knowledge of the existing relations, you would have to write SQL to do so; the DB Explorer works (almost) completely without SQL in the GUI.
Actually, the DB Explorer isn't shareware but freeware. The only restriction is that it can be used in a stand-alone / Client/Server configuration only. As soon as you want to use the tool in a three-tier or/and multi-user configuration, an evaluation period of 1 month will start.
Why don't you give it a try?|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.
Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.
I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Thanks,
Isaac|||Then your cheapest solution is to create an Access Data Project linked to your SQL Server database, with an entry form for each table.|||...Even cheaper would be to create updateable views and grant all permissions to those rather than the tables themselves.|||I think he is looking for a simple user interface, but it would definitely be a good idea to create the views you suggested, along with a login/role that only has access to those views, before allowing people into the database through an Access Data Project. Once they get into the .ADP file, they can get into anything their login allows, or that is poorly secured.|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.
Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.
I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Isaac
Please note that the DB Explorer allows you to present just the tables you need, with functional table and field names. There is also a quite complex access control mechanism integrated, which would allow you to grant read-write access to lookup tables, and read-only access or no access at all to other tables.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.
DBForums has a Marketplace forum if you wish to advertise.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.
Which part of the freeware property or extended functionality which is definitively NOT possible with Enterprise Manager didn't you understand?|||So your software is free? My apologies...|||Apologies granted. :cool:
Donatations, however, are always appreciated.|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?
I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.
Thanks!|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?
I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.
Thanks!
Thank you for pointing me for that, the label is misleading. I've changed it. Note, that for trying the software, you will need to have MDAC version 2.8 or higher.
Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table.
We can certainly write this but before reinventing the wheel I figure I'd ask and see.
Many Thanks,
IsaacThe DB Explorer works extensively with look-ups. You will have to define the relationship by referential constraints in the database, or as navigation paths in the client front-end.
You find all resources at MayeticVillage (http://www.MayeticVillage.com/DB-Explorer)
I'll be glad to answer all your questions about it.|||You can edit table data directly in Enterprise Manager.|||You can even use the query analyzer to maintain your data by DML statements, but I invite you to experience the ease of working with the DB Explorer.|||Gosh, I had no idea that DB Explorer was "shareware".
And all this time I've been going to the trouble of right-clicking on any table in Enterprise Manager and selecting "Open Table/Return All Rows"...|||The idea behind the DB Explorer is to exploit the essence of the relational model: the relationship between entities. To work with single tables / views is simpel, any tool including the Enterprise Manager can do so. To put a table / view into a relational context is normally the exclusive domain of client application development, but the DB Explorer is able to generate a user interface on-the-fly, including lookups.
Moreover, you can instantly use the relationships to navigate through your data: from a customer record to the related order records, or vise versa from an order to the corresponding customer. Even within the Enterprise Manager with its knowledge of the existing relations, you would have to write SQL to do so; the DB Explorer works (almost) completely without SQL in the GUI.
Actually, the DB Explorer isn't shareware but freeware. The only restriction is that it can be used in a stand-alone / Client/Server configuration only. As soon as you want to use the tool in a three-tier or/and multi-user configuration, an evaluation period of 1 month will start.
Why don't you give it a try?|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.
Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.
I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Thanks,
Isaac|||Then your cheapest solution is to create an Access Data Project linked to your SQL Server database, with an entry form for each table.|||...Even cheaper would be to create updateable views and grant all permissions to those rather than the tables themselves.|||I think he is looking for a simple user interface, but it would definitely be a good idea to create the views you suggested, along with a login/role that only has access to those views, before allowing people into the database through an Access Data Project. Once they get into the .ADP file, they can get into anything their login allows, or that is poorly secured.|||The problem I have with using enterprise mgr or query analyzer is that the users who will be doing the updates are very untechnical.
Ideally, I'd like to provide just a nice front end to the 4 or 5 tables that matter to them.
I can't overwhelm them with database names, a zillion other tables in a large list of tables, and cryptic column names etc etc etc.
Isaac
Please note that the DB Explorer allows you to present just the tables you need, with functional table and field names. There is also a quite complex access control mechanism integrated, which would allow you to grant read-write access to lookup tables, and read-only access or no access at all to other tables.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.
DBForums has a Marketplace forum if you wish to advertise.|||Please note that the poster requested a shareware solution. He did not ask for you to push your companys product by touting all the things it does which can also be done through Enterprise Manager.
Which part of the freeware property or extended functionality which is definitively NOT possible with Enterprise Manager didn't you understand?|||So your software is free? My apologies...|||Apologies granted. :cool:
Donatations, however, are always appreciated.|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?
I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.
Thanks!|||Curious. Why does your website offer a trial-version of DB Explorer if it is freeware?
I don't want to bother with the demo version. Please post the link where I can just download the entire thing for free.
Thanks!
Thank you for pointing me for that, the label is misleading. I've changed it. Note, that for trying the software, you will need to have MDAC version 2.8 or higher.
Monday, February 20, 2012
Looking for easy way to update w/o SQL statements
My manager is interested in knowing if there is a way to update our website's SQL database using a method with excel, similar to importing.
The person who was previously in my position had imported a few hundred new products into the database with an excel spreadsheet.
Now, we would like to make updates such as a price changes or similar adjustments to a number of the products in the database. We could use a web interface, but ours requires us to find each product individually and it takes too much time. I told him that it would probably be necessary to write an SQL statement to update the tables, but we're also interested in maintaining the integrity of the database and are worried about loosing data due to a typo. Is it possible to export the db contents to an excel file, make changes, and then merge those changes into the existing database? I have tried and failed, so I am wondering if any experienced users could help me out.
Also, is there some kind of phpmyadmin for MS SQL? A free, open source alternative would be best.Create a view/function/stored procedure that will retrieve the data with desired values, bcp the data out using this object, truncate the original table, and bulk insert the data back in.|||Better check relational integrity issues first.|||create view/function/stored procedure
bcp...queryout...
alter table <table_name> nocheck constraint all
truncate table <table_name>
bulk insert...
alter table <table_name> check constraint all|||Is this in SQL Server Manger or some similar software? I guess what I'm saying is that I'm inexperienced with Microsoft SQL Server and it seems like your directions are too vague. Thanks for helping me though.|||Is the Data in Excel?
I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert
MOO|||Originally posted by Brett Kaiser
Is the Data in Excel?
I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert
MOO
The data is stored on the webserver, but we have tried exporting to excel and when I imported the changes a new table was made or something. It was kind of confusing to work with the Manager.|||Originally posted by arosboro
It was kind of confusing to work with the Manager.
That's funny...it's always confusing to work with a manager...ooooh you probably meant Enterprise Manager...
EM for short...
And DTS...
Is the data fixed width or delimited?|||One of the things you need to worry about if you export your data, modify it, and then load it back into the database is whether any relational integrity is established between your table and other database tables, either logically or physically. You could potentially screw up your application if you are not carefull.
The best way to modify data is, of course, through the interface, but failing that you really need to do your modifications within SQL Server, using SQL statements. If you are going to be using a SQL Server database then you or somebody else in your office needs to learn some of the basics, and what you are trying to do is probably not very complex. I am afraid that you are going to create and very complicated and fragile solution to this problem involving mutiple steps and technologies, when the issue could be resolved easily within SQL Server.
The person who was previously in my position had imported a few hundred new products into the database with an excel spreadsheet.
Now, we would like to make updates such as a price changes or similar adjustments to a number of the products in the database. We could use a web interface, but ours requires us to find each product individually and it takes too much time. I told him that it would probably be necessary to write an SQL statement to update the tables, but we're also interested in maintaining the integrity of the database and are worried about loosing data due to a typo. Is it possible to export the db contents to an excel file, make changes, and then merge those changes into the existing database? I have tried and failed, so I am wondering if any experienced users could help me out.
Also, is there some kind of phpmyadmin for MS SQL? A free, open source alternative would be best.Create a view/function/stored procedure that will retrieve the data with desired values, bcp the data out using this object, truncate the original table, and bulk insert the data back in.|||Better check relational integrity issues first.|||create view/function/stored procedure
bcp...queryout...
alter table <table_name> nocheck constraint all
truncate table <table_name>
bulk insert...
alter table <table_name> check constraint all|||Is this in SQL Server Manger or some similar software? I guess what I'm saying is that I'm inexperienced with Microsoft SQL Server and it seems like your directions are too vague. Thanks for helping me though.|||Is the Data in Excel?
I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert
MOO|||Originally posted by Brett Kaiser
Is the Data in Excel?
I would DTS the data to a staging table, write a sproc that performed audits on the file, and if it passed all the test do an insert
MOO
The data is stored on the webserver, but we have tried exporting to excel and when I imported the changes a new table was made or something. It was kind of confusing to work with the Manager.|||Originally posted by arosboro
It was kind of confusing to work with the Manager.
That's funny...it's always confusing to work with a manager...ooooh you probably meant Enterprise Manager...
EM for short...
And DTS...
Is the data fixed width or delimited?|||One of the things you need to worry about if you export your data, modify it, and then load it back into the database is whether any relational integrity is established between your table and other database tables, either logically or physically. You could potentially screw up your application if you are not carefull.
The best way to modify data is, of course, through the interface, but failing that you really need to do your modifications within SQL Server, using SQL statements. If you are going to be using a SQL Server database then you or somebody else in your office needs to learn some of the basics, and what you are trying to do is probably not very complex. I am afraid that you are going to create and very complicated and fragile solution to this problem involving mutiple steps and technologies, when the issue could be resolved easily within SQL Server.
Subscribe to:
Comments (Atom)