January 4, 2006 at 2:21 pm
I need to query table A, and then take the results of the query and for each individual unique stulink record returned create a new record in table B containing new information not currently contained in table A. I know how to query to get the results from table A . but what's bamboozling me is how to use the results I obtain to create a matching record (same stulink) containing the necessary information and append that into Table B.
The tables are
Table A ASCH
Table B ASAH
What I have so far is.....
select distinct asch.stulink,asch.schoolyear,asch.grade,asch.school attended
from myDBASE.ASCH
where asch.schoolyear = 2003 and asch.grade = 09 and school attended - 021
then (here's the part I'm light on ...)
create record in table B (ASAH) where
ASAH.Stulink = ASCH.stulink (to match the record with the particular student returned from the first table)
ASAH.FromDate = 09/01/02
ASAH.ToDate = 06/01/03
ASAH.schlattnum = 021 (or... the same schoolnum as returned for this stulink in the ASCH part)
ASAH.City = [Cityname]
ASAH.State = [StateName]
ASAH.GradeLevel = ASCH.Grade (from matching record returned in first part)
Thanks in advance for any sage advice on this....
Happy New year all!
January 4, 2006 at 2:36 pm
this is an insert into.
Where the results of your select will be entered to the table on the insert. You did not indicate where the information for the city and state comes from.
If it resides in the asch table then put the column name there, note how the columns listed in the insert and the columns listed in the insert are "aligned" in the same ordinal position.
Insert into TableB(stulink,FromDate,ToDate,schlattnum,City,State,GradeLevel)
select distinct asch.stulink, '09/01/02', '06/01/03', asch.[school attended], '', '', asch.grade,
from myDBASE.ASCH
where asch.schoolyear = 2003 and asch.grade = 09 and school attended - 021
January 4, 2006 at 2:46 pm
Hi...
insert into tableASAH (stulink,schoolyear,grade,school_attended)
select distinct asch.stulink,asch.schoolyear,asch.grade,asch.school_attended
from myDBASE.ASCH inner join [tableASAH] on ASAH.Stulink = ASCH.stulink and ASAH.GradeLevel = ASCH.Grade
where asch.schoolyear = 2003 and asch.grade = 09 and school_attended = 021
and ASAH.FromDate = 09/01/02 and ASAH.ToDate = 06/01/03 and ASAH.schlattnum = 021 and ASAH.City = [Cityname]
and ASAH.State = [StateName]
Regards,
Papillon
January 4, 2006 at 3:31 pm
Hmmm.. I tried your code.. which ended up looking like this.. due to the need to populate non-null fields in ASAH5031...
Insert into sasi.asah5031(status,Schoolnum,stulink,FromDate,ToDate,schlattnum,City,State,GradeLevel,
byautoproc,[sequence])
select distinct SASI.ACHS5031.Status,SASI.ACHS5031.Schoolnum,SASI.ACHS5031.stulink, '09/01/02', '06/01/03','Schoolname', 'MyTown', 'MyState', '09','Y','1'
from SASI.ACHS5031
where SASI.ACHS5031.schlyear = 2003
and SASI.ACHS5031.grade = 09
and SASI.ACHS5031.schoolattn = 021
but I get this error....
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
January 4, 2006 at 3:37 pm
This is why you often see this in the forums:
"Please post the DDL of your tables"
You are getting this because a character column in your destination table isn't large enough to hold a data value being inserted to it. When you post the DDL, the offending column is usually pretty obvious.
January 4, 2006 at 3:45 pm
D - Data
D - Dictionary
L - ????????
January 4, 2006 at 3:49 pm
http://www.google.com/search?hl=en&q=ddl+database+acronym&meta=
The CREATE TABLE statements for the objects involved. These will show the datatypes of each column. The datatypes will include the length, then we can easily spot cases where a column in the table being selected from has a larger size than the corresponding table in the inserted to table.
January 4, 2006 at 4:03 pm
Ahhh.. that makes sense...
Is there a procedure for creating the DDL for my tables?
January 4, 2006 at 4:14 pm
Enterprise Manager (EM)
Right-click on table name->All Tasks->Generate SQL script...
January 6, 2006 at 11:10 am
Ahhh .. got it...
So .. here are the two DDL's for the tables I'm working with
First, ACHS
REATE TABLE [SASI].[ACHS5031] (
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STULINK] [numeric](5, 0) NOT NULL ,
[SEQUENCE] [numeric](3, 0) NOT NULL ,
[COURSE] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSTITLE] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TCHNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MARK] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONDUCT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREDATMPT] [numeric](5, 3) NULL ,
[CREDEARNED] [numeric](5, 3) NULL ,
[GRADE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CALMONTH] [numeric](2, 0) NULL ,
[CALYEAR] [numeric](4, 0) NULL ,
[SCHLYEAR] [numeric](4, 0) NULL ,
[TERM] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ATT1] [numeric](2, 0) NULL ,
[ATT2] [numeric](2, 0) NULL ,
[REPEATTAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MARKTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PARTNUM] [numeric](2, 0) NULL ,
[NUMOFPARTS] [numeric](2, 0) NULL ,
[CURRICULUM] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OFFSITETAG] [numeric](3, 0) NULL ,
[SCHOOLATTN] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MARKFLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSTYPECOD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NONDSTCRS] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBAREACD1] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBAREACD2] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBAREACD3] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TCHCOMMCOD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WAIVERCODE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[HNRPTSADJ] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREDITTAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD3] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD4] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERSTAMP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATESTAMP] [datetime] NULL ,
[TIMESTAMP] [numeric](6, 0) NULL ,
[SCHATTNSEQ] [numeric](3, 0) NULL ,
[SOCSECNUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DISTNUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSFLAG1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSFLAG2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSFLAG3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRSFLAG4] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ELAINSTYP] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ELAINSTLNG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD5] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CATEGORY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBJAREA1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBJAREA2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SUBJAREA3] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U$SCHLYEAR] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[U$COURSE] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Second ... ASAH
if exists (select * from dbo.sysobjects where id = object_id(N'[SASI].[ASAH5031]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SASI].[ASAH5031]
GO
CREATE TABLE [SASI].[ASAH5031] (
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STULINK] [numeric](5, 0) NOT NULL ,
[SEQUENCE] [numeric](3, 0) NOT NULL ,
[FROMDATE] [datetime] NULL ,
[TODATE] [datetime] NULL ,
[SCHLATTNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLATTNM] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CITY] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROVINCE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRADELEVEL] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MEMBERSHIP] [numeric](5, 1) NULL ,
[ABSENT] [numeric](5, 1) NULL ,
[PRESENT] [numeric](5, 1) NULL ,
[BYAUTOPROC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EOYSTATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RESERVED] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FILLER] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLFLAG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLYEAR] [numeric](4, 0) NULL ,
[DISTNO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FLSCHLNO] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERSTAMP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATESTAMP] [datetime] NULL ,
[TIMESTAMP] [numeric](6, 0) NULL ,
[U$DATE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
January 6, 2006 at 11:20 am
Well, I kinda hoped you would look through the DDL and compare it to your INSERT statement.
eg:
[STATE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
State is varchar(2)
Look what you're trying to insert into it:
Insert into sasi.asah5031(status,Schoolnum,stulink,FromDate,ToDate,schlattnum,City,State,GradeLevel,
byautoproc,[sequence])
select distinct SASI.ACHS5031.Status,SASI.ACHS5031.Schoolnum,SASI.ACHS5031.stulink, '09/01/02', '06/01/03','Schoolname', 'MyTown', 'MyState', '09','Y','1'
The error is simply telling you that a seven character hard-coded constant isn't going to fit into a 2 character destination column.
January 19, 2006 at 10:00 pm
Sorry for the delay on this.... got sent out on a "road trip". I've found another glitch in this project... the records when appended to the target table must have a increment upward a sequence number makin sure it is one higher than the existing number for the existing stulink, and if there isn't an existing match for stulink in the target table, the sequence number in the appended record (which will be the first in the sequence) must be assigned the number"1" sounde like a "case" statement... another lite area for me....
I'm getting a headache..
I'm guessing this will involve using variables and the "max +1" syntax.. but I'm pretty light on the details.
Any references anyone can point me at for reading up on variables would be helpful.
Thanks all!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply