I have an Excel spreadsheet in which I imported into a Temp table.  The
temp table script is:

CREATE TABLE [dbo].[Temp](
    [year] [float] NULL,
    [week] [float] NULL,
    [teams] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [away/home] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [score] [float] NULL,
    [first downs] [float] NULL,
    [# rushes] [float] NULL,
    [rushing yds] [float] NULL,
    [passing yds] [float] NULL,
    [p completions] [float] NULL,
    [p attempts] [float] NULL,
    [interceptions] [float] NULL,
    [return yds] [float] NULL,
    [# sacks allwd] [float] NULL,
    [sack yds lost] [float] NULL,
    [# punts] [float] NULL,
    [avg# punt] [float] NULL,
    [# fumbles] [float] NULL,
    [# fmbls lost] [float] NULL,
    [# penalties] [float] NULL,
    [pen yds lost] [float] NULL,
    [time of possession] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
    [quarter 1] [float] NULL,
    [quarter 2] [float] NULL,
    [quarter 3] [float] NULL,
    [quarter 4] [float] NULL,
    [over-under / pointspread] [float] NULL,
    [day of week / overtime?] [nvarchar](255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
    [minutes] [int] NULL,
    [seconds] [int] NULL,
    [new time] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [id] [int]
) ON [PRIMARY]

GO

Here is the sample data:

INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'NO', N'A', 13, 8, 16, 55, 147, 12, 30, 2, 10, 1, 9,
6, 45, 3, 0, 7, 61, '1899-12-30', 3, 3, 0, 7, 34.5, N'SU', 22, 15,
N'22:15     ', 1)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'PHI', N'H', 15, 21, 40, 186, 134, 18, 25, 0, 79, 6,
31, 5, 43, 5, 4, 3, 30, '1900-01-01', 6, 3, 0, 6, -3, NULL, 37, 45,
N'37:45     ', 2)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'LA', N'A', 7, 15, 22, 66, 149, 19, 38, 4, 5, 3, 21,
6, 43, 2, 0, 7, 60, '1900-01-01', 0, 7, 0, 0, 44.5, N'SU', 30, 11,
N'30:11     ', 3)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'BUF', N'H', 40, 23, 33, 207, 156, 19, 30, 1, 157, 2,
9, 4, 42, 2, 0, 12, 75, '1900-01-01', 14, 13, 7, 6, -11, NULL, 29, 49,
N'29:49     ', 4)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'DET', N'A', 24, 17, 23, 121, 233, 18, 26, 1, 6, 6,
40, 5, 43, 2, 0, 8, 44, '1900-01-01', 0, 10, 0, 14, 36.5, N'SU', 29, 9,
N'29:9      ', 5)


INSERT INTO [dbo].[Temp] ([year], [week], [teams], [away/home], [score],
[first downs], [# rushes], [rushing yds], [passing yds], [p
completions], [p attempts], [interceptions], [return yds], [# sacks
allwd], [sack yds lost], [# punts], [avg# punt], [# fumbles], [# fmbls
lost], [# penalties], [pen yds lost], [time of possession], [quarter 1],
[quarter 2], [quarter 3], [quarter 4], [over-under / pointspread], [day
of week / overtime?], [minutes], [seconds], [new time], [id])
VALUES (1992, 1, N'CHI', N'H', 27, 21, 29, 140, 227, 19, 30, 0, 85, 0,
0, 4, 43, 4, 0, 7, 55, '1900-01-01', 7, 3, 0, 17, -3.5, NULL, 30, 51,
N'30:51     ', 6)


As you can see the first game consists of the first two rows (the home
and away teams), the second game is rows 3 and 4, and the third game is
rows 5 and 6.

I need to transform the temp table data into the Schedule table and
GameScores table.  Basically taking rows one and two in the temp table
and creating one row in the Schedule table and one row in the GameScores
table (and then rows 3&4, 5&6...).  How can I do this without having to
manually enter this data?

Here is the script for the Schedule and GameScores tables:

CREATE TABLE [dbo].[Schedule](
    [ScheduleId] [int] IDENTITY(1,1) NOT NULL,
    [Year] [int] NULL,
    [Week] [int] NULL,
    [Day] [smallint] NULL,
    [Time] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HTeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ATeam] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
    [ScheduleId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[GameScores](
    [ScheduleId] [int] NOT NULL,
    [HScore] [int] NULL,
    [AScore] [int] NULL,
    [HQtr1] [int] NULL,
    [HQtr2] [int] NULL,
    [HQtr3] [int] NULL,
    [HQtr4] [int] NULL,
    [HOT] [int] NULL,
    [AQtr1] [int] NULL,
    [AQtr2] [int] NULL,
    [AQtr3] [int] NULL,
    [AQtr4] [int] NULL,
    [AOT] [int] NULL,
    [Overtime] [bit] NULL,
CONSTRAINT [PK_GameScores] PRIMARY KEY CLUSTERED
(
    [ScheduleId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



*** Sent via Developersdex http://www.developersdex.com ***

Throw out everything you have and start over with usable DDL.  The
floats are all wrong (did anyone run 3.141592543 yards at the Pi Bowl
game?), you have BIT data, the data element names are display lines and
you have no keys nor any possibility of keys.  YOu seem to put both
teams in one column.  You do know that # is not allowed in standard SQL
or most other programming language, don't you?  That SQL has temporal
data types. That you do not store computations like averages in tables.
I really got a kick out of the [day of week / overtime?] NVARCHAR
(255) column.  It stores to totally different kinds of things in one
place *and* has a data type that is unsuitable for either for them.

If I put thisd in a book as a bad exmaple, people would think I made it
up.

Just how little do you know --celko--?

This is the format it comes in by default when you import stuff using the
gui, but you wouldn't know that because I seriously doubt you've even
installed SQL Server!

And bit, there is nothing wrong with using it - just because it doesn't fit
with YOUR portability model that is used in .000000x% of implementations
doesn't mean its wrong - so long as the code is well definied and documented
and the application understands what the meaning of 0 or 1 is.

Show quoteHide quote

Are you kidding me?  The Excel spreadsheet was provided to me as is.
All I was doing was bringing into a TEMP table (and I stress TEMP) so
that I can manipulate and put into the real structure.  If you would
have taken the time to read the the post, the both teams in one column
is one of the reasons why I have to do this.  

Once again one of your posts provides no help...



*** Sent via Developersdex http://www.developersdex.com ***

JP (jp@nospam.com) writes:
Here is a query to fill Schedule:

   INSERT Schedule (ScheduleId, Year, Week, Day, HTeam, ATeam)
     SELECT a.id/2 + 1, a.year, a.week, NULL,
            CASE a.[away/home] WHEN 'H' THEN a.teams ELSE b.teams END,
            CASE b.[away/home] WHEN 'A' THEN b.teams ELSE a.teams END
     FROM   Temp a
     JOIN   Temp b ON a.id + 1 = b.id
     WHERE  a.id % 2 = 1
   go

I have here made a change to your table: I have removed the
IDENTITY property. That makes life easier.

I would also recommend that you specify NOT NULL for columns where you
don't want to permit NULL. I would also recommend that you add this
constraint:

  CONSTRAINT ckt_notsametesm (ATeam <> HTeam)

I did not fill in Day and Time, because I did not know how to determine
that from the data in Temp.

Hopefully you can populate GameScores based on the above.

Erland,

Thank you very much!  I think I can take care of the GameScore based on
your example.   Time is not provided in the original spreadsheet that I
received and will have to be entered manually.  Day is a little weird in
that this is defined in the "day of week / overtime" column.  The day is
in the first line and the overtime data flag is on the second line.




*** Sent via Developersdex http://www.developersdex.com ***

Returning selected rows
Create view of winning lottery numbers
Performance issues using Parameters
Dynamic SQL Problem..
Timeout - max pool size reached