Results 1 to 9 of 9

Thread: Concatenated numbers and more

  1. #1
    Join Date
    Apr 2004
    Posts
    21

    Concatenated numbers and more

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    21

    Question 2

    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

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    may be you inserted twice.

    use distinct clause and try.

  5. #5
    Join Date
    Apr 2004
    Posts
    21

    Question 3

    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

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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'

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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

  8. #8
    Join Date
    Apr 2004
    Posts
    21

    Question 4

    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.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •