|
hi iam using sql server 2000
iam having two tables with same fields
my first tableA contains the fields
Orderid int primary key identity(automatically increment)
productname nvarchar
price nvarchar
quantity nvarchar
total nvarchar
my second tableB contains the fields
Orderid int (no primary key)
productname nvarchar
price nvarchar
quantity nvarchar
total nvarchar
from asp.net iam entering multiple values at a time in tableA, at the same time i want to insert into tableB
can you give the querry which helps me
|
|
|
|
|
A stored procedure that performs the operation should do it. Or you could put a trigger on TableA if you liked.
I'd question your datatypes for fields like price, quantity, total etc. Shouldn't be using strings for what looks like numeric information
CREATE PROCEDURE CreateOrderDetails
(
@ProductName NVARCHAR(50),
@Price NVARCHAR(10),
@Quantity NVARCHAR(10),
@Total NVARCHAR(10)
)
AS
SET NOCOUNT ON
DECLARE @MyRecordId INT
INSERT INTO
TableA (productname, price, quantity, total)
VALUES
(@ProductName, @Price, @Quantity, @Total)
--Get the ID of the record we just created
SET @MyRecordId = SCOPE_IDENTITY()
INSERT INTO
TableB (Orderid, productname, price, quantity, total)
SELECT
Orderid, productname, price, quantity, total
FROM
TableA
WHERE
Orderid = @MyRecordId
RETURN @@ERROR
SET NOCOUNT OFF
|
|
|
|
|
I'm curious, what is the logic for creating such a data structure, why are you storing data twice.
And yes you really should change you data types away from the default, it will cause you no end of problems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey by using trigger u can do this. Trigger can used to implemnt business rules
|
|
|
|
|
I wouldn't do this since triggers can slow down the DB. Stored procedure or sep. calls is better. If it needs to be all or nothing and assuming the inserts are single row, wrap in a transaction.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
You can make your stored procedure save same data in both the tables (two insert statements). BTW, why are you doing this? Don't you think it is weird?
|
|
|
|
|
Hi all,
This is how im getting columns from 3 tables....
select projectID=projects.projectid,projectname,
[image],country,county,ClientCompany,city,ConstructionCompletionDate_dt,
OverallBuildingSize_uts from projects, schedule,BuildingSiteStatics
where projects.projectid=schedule.projectid and projects.projectid=BuildingSiteStatics.projectid
I have searched from 2 tables using following query
select a.*,b.ConstructionCompletionDate_dt from projects a join
schedule b on a.projectid=b.projectid where projectname like '%%' or
country like '' or clientcompany like '%%' or
ConstructionCompletionDate_dt like '%%'
Now i need to search columns from 5 tables How can i do it....
|
|
|
|
|
test-09 wrote: Now i need to search columns from 5 tables How can i do it....
Obviously, you need to join the remaining tables with your existing ones and filter the data from the corresponding columns.
When you fail to plan, you are planning to fail.
|
|
|
|
|
As said, jion all 5 tables.
If you expect to reuse the 5 table join in other queries I would create a view, makes life much simpler.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
select projectID=projects.projectid,projectname,
[image],country,county,ClientCompany,city,ConstructionCompletionDate_dt,
OverallBuildingSize_uts from projects, schedule,BuildingSiteStatics
where projects.projectid=schedule.projectid and projects.projectid=BuildingSiteStatics.projectid
using above query how can i join 5 tables..
|
|
|
|
|
This query uses what I call Oracle joins where the links are in the where clause, never to be seen in any database I own. Use the propert join methods. Here is a sample from a view I have
FROM dbo.FactConsoTB AS F
INNER JOIN dbo.Branch AS B ON F.BranchID = B.BranchID
INNER JOIN dbo.lnkAccountProduct AS L ON F.APLinkID = L.APLinkID
INNER JOIN dbo.Account AS A ON L.AccountID = A.AccountID
INNER JOIN dbo.Product AS P ON L.ProductID = P.ProductID
INNER JOIN dbo.Currency AS C ON F.CurrencyID = C.CurrencyID
INNER JOIN dbo.Currency AS BC ON F.BaseCurrencyID = BC.CurrencyID
INNER JOIN dbo.ProductCategory ON P.ProductCategoryID = dbo.ProductCategory.ProductCategoryID
test-09 wrote: select projectID=projects.projectid
Why are you useing this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
using view i tried this but im getting error
"Ambiguous column name 'ConstructionCompletionDate_dt'"
what wrong im doing....
SELECT dbo.Projects.ProjectID, dbo.Projects.ProjectName,
dbo.Projects.City, dbo.Projects.County, dbo.Projects.Country,
dbo.Projects.ClientCompany,
dbo.BuildingSiteStatics.OverallBuildingSize_uts,
dbo.Schedule.ConstructionCompletionDate_dt,
dbo.OverallDevelopment.TotalNumberDwellings
FROM dbo.Projects INNER JOIN
dbo.BuildingSiteStatics ON dbo.Projects.ProjectID = dbo.BuildingSiteStatics.ProjectID INNER JOIN
dbo.OverallDevelopment ON dbo.Projects.ProjectID
= dbo.OverallDevelopment.ProjectID INNER JOIN
dbo.Schedule ON dbo.Projects.ProjectID = dbo.Schedule.ProjectID where projectname like '%%' or country like ''
or clientcompany like '%%' or ConstructionCompletionDate_dt like '%%'
|
|
|
|
|
Prefix the column name with the tablename or the alias.
Also try Google and BOL, the response time is generally quicker and gentler.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
test-09 wrote: Now i need to search columns from 5 tables How can i do it....
One alternative would be to build some views, consisting of a tablename, columnname and a varchar representing the value. Create a union of those views to search all columns with a single select-statement and the LIKE operator.
What database are you using? If you're on Sql Express, have you considered using the Full Text Search[^] component?
I are Troll 
|
|
|
|
|
I'm looking for a way to concatenate all the fields in a result set to one value. If I know the names of the fields, it is fairly easy:
SELECT Field1 + Field2 + Field3 FROM MyTable
although it is a bit more tricky if one or more of the fields are numeric in which case the following would do:
SELECT CAST(Field1 AS VARCHAR) + CAST(Field2 AS VARCHAR) + CAST(Field3 AS VARCHAR) FROM MyTable
The above snippets would return a long string (a concatenation of all the fields) for each row in the table.
But what if I don't know the names, number of and types of fields in the table? Is there a way to still accomplish the above?
Thanks in advance.
|
|
|
|
|
You would have to mess around with sys.columns sys.types to determine the datatype of the columns dynamically. Of course you would have to know the table and column name before trying to get the datatype.
OR
Are you constrained by using TSQL ?
If you write a .NET application and put the results into a Datatable, you could then find the datatype by using something like: Datatable.Columns.GetType()
Good luck 
|
|
|
|
|
Thanks,
I was hoping to be able to do it using SQL only but I eventually decided to do it from inside a C# app which does make it a lot simpler.
|
|
|
|
|
You can use syscolumns or the system views and basically build a string in TSQL using a loop or cursor and the execute the string. This is a real kludge but there is no other way that I know of. Seems like a rather silly requirement!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah it probably is a silly requirement. The reason I wanted to do so is as follows.
I have a number of tables that I need to check for changes on a regular basis. I can't add columns to the tables so I have to compare them against "backup copies". I decided to create a mirror table for each that holds the last saved version of the table. When changes are made to the original table I wanted my script to compare the table against mine and then process only those rows that are different.
To compare an entire row with my backup table's row, I thought I'd return the row as I described above (ie a long concatenated string). This way I wouldn't need to compare it field by field.
In the end I decided to do most of the work in C# anyway so it didn't matter that I'd have to compare the rows field by field.
|
|
|
|
|
I would use a different approach, I presume you can add a new table to the database even if you can't change an existing table.
So have a log table to record changes, depending on requirements it could be as little as and Id, modified and modifiedby columns for each table you are monitoring. Changes can be logged by spit triggers, this is one of the very few valid uses for triggers.
If you need a from => to audit then a more detailed logging process can be implemented.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day
Application Design
My application is a web application that works on databases. Meaning that there is a login page that has list of Database. A user logs into the database and do whatever on that database. Now a user can create a new Database from the front end and login to it and do whatever the application does, The new Database is created from a Master Database, this simply means that when a user creates a new Database, i do the following.
Create a new Database--> Restore the Master Database over the newly created database. Now this is fine but i have a challenge. My Challenge is that a user can 10 Databases and i will have Different clients from Different geographic places and if i find a bug in a stored procedure or if i want to change on stored procedure , that means i have to go to each client and update every database. i use Redgate to Script the Master Database.
Challenge
I am looking for a Way to Update all the Databases for each SQl instance in a client centrally without connection to a client and do that manually. if the clients are many it takes too much time.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Holy sh*t, my skin actually crawled as I read this post. Are you freaking nuts, this is the WORST data design you could possibly come up with.
Red-Gate will mitigate the problem, SQL Compare is an excellent product but you are maintaining multiple code bases because you cannot segregate you data within a single database. This is elementary database stuff, your solution stinks, phew, and will/is cause you nightmares.
Vuyiswa Maseko wrote: Challenge
That's not a challenge that a bloody disaster!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft am not sure why you start cursing but i will decide not to be offended by what you just wrote. I think you should look at your forum guideline and revisit rule number 8
http://www.codeproject.com/Messages/1278600/How-to-get-an-answer-to-your-question.aspx[^]
This is a Project i inherited and i must admit a poor decision has been taken from the beginning and we i can see the decesion takers of that project i will definitely shoot one of them, but now it will not help.
Getting back to the problem. To maintain this application this way its a nightmare and i cant run a script to update a database every a user needs to login. Currently am looking for a thought and in the next version of the Software i will be looking at Redesigning the data structure for this application.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: This is a Project i inherited
You have my sympathies. Red-Gate have a product for running a script on multiple database from a single point, I have never looked at it as I only have the 3 (dev/UAT/Production) but it may be useful to this nightmare.
I can understand the desire to redesign, the only reason you should have been offended is if you design the structure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understand.
Thank you for your help
Kind Regards
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
