Post reply

insert data into a table from another table, while sorting according to a field.

  • April 20, 2012 at 1:42 am

    #260287

    Hey,

    I am making attendance management system in VS2010 with RFID technology, doing all my backend data manipulation in SQL server 2005, I know basic SQL, but I'm going to need some help with this this issue.

    I have the following tables:

    facultymasterdata, timings and rawdumps - data in rawdumps is saved from a serial port data logger program I wrote in vb.net, fid being the RFID tag ID, timecode being A,B,C or D (for identifying time as intime-A, lunchout-B, lunchin-C, outtime-D) and currtime is the current system time. I get the RFID tag IDs via a microcontroller that adds the time code according to physical button presses.

    For example 4C00A2C82A0C-A, which I split using substrings and insert into the table.

    What I need to do is sort the all the data from 'rawdumps' and copy it into 'timings' according to the timecode. The fid and currtime fields in 'rawdumps' which will have a timecode as "A" get saved into the field intime_a in timings similarly time code of B will be saved under the field lunchout_b. AND also the fname from 'facultymasterdata' corresponding to the fid.

    Basically I have these:

    And I need to get this:

    Sample data and DDL scripts:

    create table rawdumps

    (

    fid varchar(max) PRIMARY KEY CLUSTERED,

    timecode char(10),

    currtime datetime

    )

    insert into rawdumps values('4C00A2C82A0C','A','6:07:51 PM')

    insert into rawdumps values('4C00A2C82A0C','B','7:07:51 PM')

    insert into rawdumps values('4C00A2C82A0C','C','8:07:51 PM')

    insert into rawdumps values('4C00A2C82A0C','D','9:07:51 PM')

    create table timings

    (

    fname varchar(max),

    fid varchar(max) PRIMARY KEY CLUSTERED,

    intime_a

    outtime_d

    lunchout_b

    lunchin_c

    )

    insert into timings values('Abc','4C00A2C82A0C','6:07:51 PM','9:07:51 PM','7:07:51 PM','8:07:51 PM')

    create table facultymasterdata

    (

    fname varchar(max),

    fid varchar(max) PRIMARY KEY CLUSTERED,

    dept varchar(max)

    title varchar(max)

    phone char(10)

    dob datetime

    )

    insert into facultymasterdata values('Abc','4C00A2C82A0C','ECE','Undergrad Student','8801589940','26/05/1988')

    I tried playing around with something like this:

    and I get this error "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

    insert into timings (fname,fid,intime_a,outtime_d,lunchout_b,lunchin_c)

    select a.fname, b.fid, b.currtime

    from facultymasterdata a, rawdumps b

    where a.fid = b.fid

    order by b.timecode

    Any help of info will be appreciated.

    Thanks a lot.

  • April 20, 2012 at 2:58 am

    #1476474

    salam 52473 (4/20/2012)

    Hey,

    I am making attendance management system in VS2010 with RFID technology, doing all my backend data manipulation in SQL server 2005, I know basic SQL, but I'm going to need some help with this this issue.

    I have the following tables:

    facultymasterdata [fname,fid,dept,title,phone,dob]

    timings [fname,fid,intime_a,outtime_d,lunchout_b,lunchin_c]

    rawdumps [fid,timecode,currtime] - data here is saved from a serial port data logger program I wrote in vb.net, fid being the RFID tag ID, timecode being A,B,C or D (for identifying time as intime-A, lunchout-B, lunchin-C, outtime-D) and currtime is the current system time. I get the RFID tag IDs via a microcontroller that adds the time code according to physical button presses.

    What I need to do is sort the all the data from 'rawdumps' and copy it into 'timings' according to the timecode. The [fid] and [currtime] fields in 'rawdumps' which will atime code as "A" get saved into the field [intime_a] in timings similarly time code of B will be saved under the field [lunchout_b]. AND also the [fname] from 'facultymasterdata' corresponding to the [fid].

    Any help of info will be appreciated.

    Thanks a lot.

    Hello and welcome to SSC!

    I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data and ddl scripts.

    If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.

    Thanks!

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 20, 2012 at 3:55 am

    #1476497

    Cadavre (4/20/2012)

    Hello and welcome to SSC!

    I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data and ddl scripts.

    If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.

    Thanks!

    Hello,

    Thanks for the info, I edited the opening post, do let me know if it needs further changes.

  • April 20, 2012 at 8:39 am

    #1476694

    abs1337 (4/20/2012)

    I tried playing around with something like this:

    and I get this error "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

    insert into timings (fname,fid,intime_a,outtime_d,lunchout_b,lunchin_c)

    select a.fname, b.fid, b.currtime

    from facultymasterdata a, rawdumps b

    where a.fid = b.fid

    order by b.timecode

    This error message is fairly self-explanatory. The number of columns in your source must match the number of columns specified in your destination. Here, your source has three columns, but you've specified six columns in your destination. The numbers don't match, so you're getting an error.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • April 20, 2012 at 8:52 am

    #1476706

    And just because nobody else has said it yet...the order of data when you insert is irrelevant. SQL tables have no order so ordering your data before an insert does not mean the data is sorted. In fact, sql does not guarantee the order of an insert. If you want your data sorted there is one and only one way to do that, add an order by clause to your select statements when retrieving data.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • April 20, 2012 at 8:57 am

    #1476713

    drew.allen (4/20/2012)

    This error message is fairly self-explanatory. The number of columns in your source must match the number of columns specified in your destination. Here, your source has three columns, but you've specified six columns in your destination. The numbers don't match, so you're getting an error.

    Drew

    Yea I could understand that much, as I said my knowledge in SQL is limited to just the basics, I knew from the beginning I wouldn't be able to solve this problem on my own. I knew that SQL script above wouldn't work, it was just something I was playing around with it, its all I could come up with.

    Sean Lange (4/20/2012)

    And just because nobody else has said it yet...the order of data when you insert is irrelevant. SQL tables have no order so ordering your data before an insert does not mean the data is sorted. In fact, sql does not guarantee the order of an insert. If you want your data sorted there is one and only one way to do that, add an order by clause to your select statements when retrieving data.

    I don't really need them in any particular order, as you can see my rawdata comes into the table in the order of A > B > C > D and I save that data in the final table in the order A(in), D(out), B(lunchout), C(lunchin). I just need to sort them according to the timecode. A goes under A, B goes under B and so on.

    Thanks

  • April 20, 2012 at 9:46 am

    #1476769

    Check out Jeff Moden's article Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • April 20, 2012 at 10:45 am

    #1476807

    drew.allen (4/20/2012)

    Check out Jeff Moden's article Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Drew

    Hey, that's exactly what I needed.

    Thanks a lot!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy