Printable View
Please MS SQL only for your reply. And thanks again so much. The help is just unmeasureable for us.
QUESTION: We are successfully using an ASP.NET form to enter data into a table in our MS SQL database via the web. Now we need to concatenate two of the ID numbers and copy the concatenated number and the data in five other columns to another table in the database. No idea how to do this. Can the instruction be appended to the webform or is there a better way? What command should be used?
create table x (id int, id2 int, name varchar(100), bill money, others varchar(100))
insert into x select 1,1,'a',20.00,'x3'
insert into x select 3,1,'av',230.00,'x4'
insert into x select 3,2,'ab',240.56,'rx'
insert into x select 5,3,'an',200.50,'rx'
insert into x select 23,41,'ma',3204.00,'fx'
insert into x select 17,15,'a,',2204.56,'xb'
Create table concattable (name varchar(1000))
insert into concattable select convert(varchar(20),id)+
convert(varchar(20),id2)+name+
convert(varchar(20),bill)+others from x
select * from concattable
--results
11a20.00x3
31av230.00x4
32ab240.56rx
53an200.50rx
2341ma3204.00fx
1715a,2204.56xb
In MS SQL only -- your answer above helped Much! but we now have this Question 2 on the duplication.
Question 2: We have an MS SQL database that works well EXCEPT for one very large table (a million + records). There are not many columns but a lot of records. Any query we run, whether from a webform or directly on the server from Query Manager, results in any record being returned twice. It is an identical copy of the record, including record number. This is preventing us from editing records and from displaying data. This does not occur with any of our other tables.
Ideas? Any clues GRAEATLY appreciated
may be you inserted twice.
use distinct clause and try.
The Select Distinct did return only one record - without it, it returned two copies of the same record, including the same record number. I found that the record number was NOT set as an identity field. I set it as an identity field and got errors when returning all rows.
I deleted the identity column and created a new one. A search now returns two copies of the record with different Identity keys. Apparently in the import, the records managed to duplicate themselves.
QUESTION 3:
Now, the question is, is there a way to delete the duplicate columns by keying on another column that should be distinct?
Thanks so much, in advance
--See below example
--Simulate duplicates
create table duplicatetable (name varchar(100),
sal int, allowance int)
insert into duplicatetable select 'a',1002,510
insert into duplicatetable select 'a',1002,510
insert into duplicatetable select 'b',1020,210
insert into duplicatetable select 'b',1020,210
insert into duplicatetable select 'c',120,20
insert into duplicatetable select 'c',120,20
insert into duplicatetable select 'c',120,20
--Remove duplicates (method 1)
--create new table
select * into distincttable from duplicatetable where
1=0
--insert distinct values
insert into distincttable select distinct * from
duplicatetable
--drop original table
drop table duplicatetable
--rename new table to original table
sp_rename 'distincttable','duplicatetable'
--Simulate duplicates (method 2)
create table duplicatetable (name varchar(100),
sal int, allowance int)
insert into duplicatetable select 'a',1002,510
insert into duplicatetable select 'a',1002,510
insert into duplicatetable select 'b',1020,210
insert into duplicatetable select 'b',1020,210
insert into duplicatetable select 'c',120,20
insert into duplicatetable select 'c',120,20
insert into duplicatetable select 'c',120,20
--Add identity column
alter table duplicatetable add id int identity(1,1)
--delete duplicates
delete from duplicatetable where
id not in (
Select max(id) as ID from duplicatetable
group by name,sal,allowance)
--remove identity column
alter table duplicatetable drop column id
select * from duplicatetable
MAX -- thank you -- it has been quite sometime since I have had to use COBOL. My partner is "book learning. and I might steer her incorrectly" Her question, she is on the server end, is
QUESTION 4:
I don' t understand what the coding means so I am not sure how to do it. I am guessing that the a, b, c represent column names but don't know what the numbers after are or how to use what on what column. Just need to understand what the numbers represent so do the right thing with them.
name Sal and allowance are the column names.
Sal and allowance store integer values. This table is a sample simulation on how to delete duplicates.