Need to insert multiple records into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to insert multiple records into one row

Author

Topic

michpaus
Starting Member

10 Posts

Posted - 2011-07-27 : 12:01:59

I have a temp table that currently displays like this (using examples, not real data)

TestDate1
MathScore1
ReadingScore1
WritingScore1
TestDate2
MathScore2
ReadingScore2
WritingScore2
TestDate3
MathScore3
ReadingScore3
WritingScore3

I need to pull that data into a table and have the individual records combined i.e.

TestDate (should have TestDate1, Testdate2 and TestDate3 in one field) and so on for the other fields.

Any suggestions on how to do this? My first thought was to create a cursor to do this but I am a newbie with cursors and not sure how to start. Is what I am asking even possible?

Thanks for any help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 12:07:19

Create a table and then do three insert statements as I am showing below. See if that is what you need:


CREATE TABLE #tmp (TestDate DATETIME, MathScore INT, ReadingScore INT, WritingScore INT);

INSERT INTO #tmp SELECT TestDate1, MathScore1, ReadingScore1, WritingScore1 FROM YourCurrentTable;
INSERT INTO #tmp SELECT TestDate2, MathScore2, ReadingScore2, WritingScore2 FROM YourCurrentTable;
INSERT INTO #tmp SELECT TestDate3, MathScore3, ReadingScore3, WritingScore3 FROM YourCurrentTable;

michpaus
Starting Member

10 Posts

Posted - 2011-07-27 : 12:56:19

Sunita, I already have a table created. It has some more fields that just the testdate and relevant scores. What I am trying to do is pull the multiple rows from my temp table into the staging table I created. That's why I thought a cursor would work because it would basically look at testdate and if there was a value then insert tesdate1, math1, reading1 and writing1 and then check for other values and if they exist insert that record i.e. Testdate2 etc.

I will try the insert and see if that works though. Thank you for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 13:56:22

I am not quite following the requirements that you listed. Can you post some sample input data and corresponding output data that would demonstrate the business requirements? Brett's blog might help.

michpaus
Starting Member

10 Posts

Posted - 2011-07-27 : 14:09:57

Hi Sunita

This is the table that has the multiple records. This table is populated by a flat file

Create table CC_SatScores_Pre (
[SATIdentity] [int] IDENTITY(1,1) NOT NULL,
last_name varchar(60),
first_name varchar(60),
middle_initial varchar(8),
gender varchar (1) ,
dob datetime,
ssn varchar (25),
home_street_address_1 varchar(120),
home_city varchar (60),
home_state varchar (2),
home_postal_code varchar (24),
county varchar(40) ,
home_country varchar (60),
home_phone varchar (23),
high_school_grad_date datetime,
TestDate datetime,
sat_reading varchar (255),
sat_math varchar (255),
sat_writing varchar (255),
TestDate2 varchar (255),
sat_reading2 varchar (255),
sat_math2 varchar (255),
sat_writing2 varchar (255),
TestDate3 varchar (255),
sat_reading3 varchar (255),
sat_math3 varchar (255),
sat_writing3 varchar (255),
TestDate4 varchar (255),
sat_reading4 varchar (255),
sat_math4 varchar (255),
sat_writing4 varchar (255),
TestDate5 varchar (255),
sat_reading5 varchar (255),
sat_math5 varchar (255),
sat_writing5 varchar (255),
TestDate6 varchar (255),
sat_reading6 varchar (255),
sat_math6 varchar (255),
sat_writing6 varchar (255),
EssayLocatorID varchar (25),
ethnicity varchar (1) ,
religious_affiliation varchar (2),
email_address varchar (255),
high_school_name varchar (60),
ceeb varchar (255),
insert_time datetime,
Athletics varchar (1),
ProgramofInterest varchar (1),
TalismaImport char (1),
TalismaIntImport char (1),
TalismaSATImport char(1),
[tTimestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_CC_CC_SatScores_Pre] PRIMARY KEY CLUSTERED
(
[SATIdentity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CC_SatScores_Pre] ADD CONSTRAINT [DF_CC_CC_SatScores_Pre_talismaimport] DEFAULT (0) FOR [talismaimport]
GO

The fields in bold above are the ones I want to pull into the bolded fields in the table below
Create table CC_SatScores_SAT_STG (

SatIdentity int,
email_address varchar (255),
EssayLocatorID varchar (25),
TestDate datetime,
sat_reading varchar (255),
sat_writing varchar (255),
sat_math varchar (255),
high_school_grad_date datetime,
high_school_name varchar (60),
ceeb varchar (255),
[lImpIdentity] [int] IDENTITY(1,1) NOT NULL,
[lImpStatus] [int] NULL,
[tTimestamp] [timestamp] NOT NULL,
[lObjectID] [int] NULL,
[lParentObjectID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[lImpIdentity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CC_SatScores_SAT_STG] ADD DEFAULT ((0)) FOR [lImpStatus]
GO

I am trying to create a stored procedure to do this and my initial thought was to create a temp table within the stored procedure to store the testdates and scores and then create a cursor that reads the first set of records (testdate1, math1, etc) and inserts it into my stg table. Then fetch the next record, if its not null, insert. If it is null end. (this is because the most recent record will always be in position 1) I don't know if that give you a clearer understanding of what I am trying to do. I am still pluggin at it.

Thank you for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-27 : 15:47:26

If you had only one row in the CC_SatScores_Pre table like shown below:

[SATIdentity]  = 1
lastname       = 'Smith'
...
TestDate       = '20100501'
sat_reading    = 800
sat_math       = 800
sat_writing    = 800
TestDate2      = '20100601'
sat_reading2   = 798
sat_math2      = 798
sat_writing2   = 798
TestDate3      = '20100701'
sat_reading3   = 800
sat_math3      = 800
sat_writing3   = 800
TestDate4      = '20100801'
sat_reading4   = 695
sat_math4      = 697
sat_writing4   = 698
TestDate5      = '20100901'
sat_reading5   = 695
sat_math5      = 697
sat_writing5   = 698
TestDate6      = '20101001'
sat_reading6   = 694
sat_math6      = 694
sat_writing6   = 694
...
[tTimestamp]   = '20110726'

What is the output you would want to see in the CC_SatScores_SAT_STG table?

What would be the objectid and parentobjectid in the CC_SatScores_SAT_STG table?

michpaus
Starting Member

10 Posts

Posted - 2011-07-28 : 09:31:27

The output I would ideally like to see is

TestDate
'20101001','20100901','20100801','20100701','20100601','20100501' (the latest date will always be first)

The other columns (math, Reading, writing) should also follow the same order as the test date.

ObjectId would be SATIdentity

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 10:01:08

TestDate column in CC_SatScores_SAT_STG table is of data type DATETIME. So it will not be able to store multiple dates in one row, nor will it be able to store that as a character string (if you were to make it a comma-separated string).

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-07-28 : 10:24:29

You should really, REALLY read up on NORMALIZATION and how to design a database. Really.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

michpaus
Starting Member

10 Posts

Posted - 2011-07-28 : 12:08:18

Sunita, thank you for all your help.

Don at Work, thanks I will follow your advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources