inserted table - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

inserted table

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 12:18:45

thru asp, what is the syntax of my sql statement to select records from the inserted table?? Is this possible??

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 12:20:44

Not possible. INSERTED is only exposed in a trigger.

<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-17 : 12:24:31

The inserted pseudo-table is only available inside a trigger. Once the INSERT or UPDATE operation completes the inserted table is not available anymore. You could write a trigger to export these results into another table though:

CREATE TRIGGER copyRows ON myTable FOR INSERT, UPDATE AS
INSERT INTO otherTable SELECT * FROM inserted
--...etc. other trigger code goes here

On the ASP side, you'd use SELECT * FROM otherTable to get them.

What exactly are you trying to do? I can see some performance problems using this approach, depending on what you're doing with the data. Can you explain a little more?

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 12:27:12

What I am trying to do is take one large table in a database created by a non-technical user and make it relational. So I have about 50 fields in this one table and need to divide the table into 5 different tables keeping an id to relate each record to each other.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 12:28:44

That doesn't explain the ASP piece . . .

<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-17 : 12:29:57

Can you post the structures of all of the tables? It'll help a great deal.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 12:31:56

I don't know about cursors and I thought that is the way to do it in SQL so I was going to create an ASP page to do it for me. I was going to select all records from this table, loop thru each one, and insert the correct fields (including an id that I would handle) into the tables i created. Sorry for the confusion.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 12:32:30

hang on and i will

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-17 : 12:35:11

There'll be a setbased method... cursors suck

-----------------------
Take my advice, I dare ya

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 12:40:46

main: (the table that will be split up)
[StudentID] [int] IDENTITY (1, 1) NOT NULL ,
[LastName] [nvarchar] (30) NOT NULL ,
[FirstName] [nvarchar] (15) NULL ,
[MidName] [nvarchar] (2) NULL ,
[SSN] [nvarchar] (13) NULL ,
[BirthDate] [datetime] NULL ,
[Sex] [nvarchar] (1) NULL ,
[EthnicBack] [nvarchar] (16) NULL ,
[Street] [nvarchar] (25) NULL ,
[City] [nvarchar] (15) NULL ,
[StateOld] [nvarchar] (15) NULL ,
[State] [nvarchar] (2) NULL ,
[ZipCode] [nvarchar] (10) NULL ,
[HighGrade] [smallint] NULL ,
[PurpPostEd] [smallint] NULL ,
[PurpEmpl] [smallint] NULL ,
[PurpMilit] [smallint] NULL ,
[PurpOther] [smallint] NULL ,
[ActiveDuty] [smallint] NULL ,
[FormatCode] [nvarchar] (2) NULL ,
[Resident] [smallint] NULL ,
[SDSchool] [smallint] NULL ,
[AdultEd] [smallint] NULL ,
[GrantedTime] [smallint] NULL ,
[PermitSpecialDev] [smallint] NULL ,
[PermitVariations] [smallint] NULL ,
[TestedUsing] [smallint] NULL ,
[CenterID] [nvarchar] (3) NULL ,
[TestCenter] [nvarchar] (40) NULL ,
[ExaminerName] [nvarchar] (30) NULL ,
[Age] [smallint] NULL ,
[DelvryDate] [datetime] NULL ,
[CertNumberPart] [int] NULL ,
[CertNumber] [nvarchar] (8) NULL ,
[Average] [float] NULL ,
[PassCode] [smallint] NULL ,
[DupCertif] [bit] NULL ,
[DupPrint] [bit] NULL ,
[DateIssued] [datetime] NULL ,
[CertifUnclaimed] [smallint] NULL ,
[CertifPrinted] [bit] NULL ,
[LetterPrinted] [bit] NULL ,
[ScorePrinted] [bit] NULL ,
[PrintScore] [bit] NULL ,
[Test1Form] [nvarchar] (2) NULL ,
[RS1M] [smallint] NULL ,
[RS1E] [smallint] NULL ,
[Test1HighScore] [smallint] NULL ,
[PR1] [int] NULL ,
[Test1HDate] [datetime] NULL ,
[Test1Pass] [smallint] NULL ,
[Test2Form] [nvarchar] (2) NULL ,
[RS2] [smallint] NULL ,
[Test2HighScore] [smallint] NULL ,
[PR2] [int] NULL ,
[Test2HDate] [datetime] NULL ,
[Test2Pass] [smallint] NULL ,
[Test3Form] [nvarchar] (2) NULL ,
[RS3] [smallint] NULL ,
[Test3HighScore] [smallint] NULL ,
[PR3] [int] NULL ,
[Test3HDate] [datetime] NULL ,
[Test3Pass] [smallint] NULL ,
[Test4Form] [nvarchar] (2) NULL ,
[RS4] [smallint] NULL ,
[Test4HighScore] [smallint] NULL ,
[PR4] [int] NULL ,
[Test4HDate] [datetime] NULL ,
[Test4Pass] [smallint] NULL ,
[Test5Form] [nvarchar] (2) NULL ,
[RS5] [smallint] NULL ,
[Test5HighScore] [smallint] NULL ,
[PR5] [int] NULL ,
[Test5HDate] [datetime] NULL ,
[Test5Pass] [smallint] NULL ,
[EntireBatteryDate] [datetime] NULL ,
[PartBatteryDate] [datetime] NULL ,
[RetestedDate] [datetime] NULL ,
[Comments] [ntext] NULL ,
[AkaName] [nvarchar] (46) NULL

student:

[StudentID] [int] IDENTITY (1, 1) NOT NULL ,
[CenterID] [nvarchar] (3) NULL ,
[LastName] [nvarchar] (30) NULL ,
[FirstName] [nvarchar] (15) NULL ,
[MidName] [nvarchar] (2) NULL ,
[SSN] [nvarchar] (13) NULL ,
[BirthDate] [datetime] NULL ,
[Sex] [nvarchar] (1) NULL ,
[EthnicBack] [nvarchar] (16) NULL ,
[Street] [nvarchar] (25) NULL ,
[City] [nvarchar] (15) NULL ,
[StateOld] [nvarchar] (15) NULL ,
[State] [nvarchar] (2) NULL ,
[ZipCode] [nvarchar] (10) NULL ,
[HighGrade] [smallint] NULL ,
[PurpPostEd] [smallint] NULL ,
[PurpEmpl] [smallint] NULL ,
[PurpMilit] [smallint] NULL ,
[PurpOther] [smallint] NULL ,
[ActiveDuty] [smallint] NULL ,
[FormatCode] [nvarchar] (2) NULL ,
[Resident] [smallint] NULL ,
[SDSchool] [smallint] NULL ,
[AdultEd] [smallint] NULL ,
[Age] [smallint] NULL ,
[Comments] [ntext] NULL ,
[AkaName] [nvarchar] (46) NULL

test:
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[StudentID] [int] NOT NULL ,
[DeliveryDate] [datetime] NULL ,
[GrantedTime] [smallint] NULL ,
[PermitSpecialDev] [smallint] NULL ,
[PermitVariations] [smallint] NULL ,
[DelvryDate] [datetime] NULL ,
[CertNumberPart] [int] NULL ,
[CertNumber] [nvarchar] (8) NULL ,
[Average] [float] NULL ,
[PassCode] [smallint] NULL ,
[DupCertif] [bit] NULL ,
[DupPrint] [bit] NULL ,
[DateIssued] [datetime] NULL ,
[CertifUnclaimed] [smallint] NULL ,
[CertifPrinted] [bit] NULL ,
[LetterPrinted] [bit] NULL ,
[ScorePrinted] [bit] NULL ,
[PrintScore] [bit] NULL ,
[EntireBatteryDate] [datetime] NULL ,
[PartBatteryDate] [datetime] NULL ,
[RetestedDate] [datetime] NULL ,
[TestedUsing] [smallint] NULL

Test Center:
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TestCenterID] [int] NOT NULL ,
[TestCenter] [char] (100) NOT NULL ,
[ExaminerName] [char] (50) NOT NULL

Test Results:
[TestResultsID] [int] IDENTITY (1, 1) NOT NULL ,
[StudentID] [int] NOT NULL ,
[TestNumber] [int] NOT NULL ,
[Test1Form] [nvarchar] (2) NULL ,
[RS1M] [smallint] NULL ,
[RS1E] [smallint] NULL ,
[Test1HighScore] [smallint] NULL ,
[PR1] [int] NULL ,
[Test1HDate] [datetime] NULL ,
[Test1Pass] [smallint] NULL ,
[Test2Form] [nvarchar] (2) NULL ,
[RS2] [smallint] NULL ,
[Test2HighScore] [smallint] NULL ,
[PR2] [int] NULL ,
[Test2HDate] [datetime] NULL ,
[Test2Pass] [smallint] NULL ,
[Test3Form] [nvarchar] (2) NULL ,
[RS3] [smallint] NULL ,
[Test3HighScore] [smallint] NULL ,
[PR3] [int] NULL ,
[Test3HDate] [datetime] NULL ,
[Test3Pass] [smallint] NULL ,
[Test4Form] [nvarchar] (2) NULL ,
[RS4] [smallint] NULL ,
[Test4HighScore] [smallint] NULL ,
[PR4] [int] NULL ,
[Test4HDate] [datetime] NULL ,
[Test4Pass] [smallint] NULL ,
[Test5Form] [nvarchar] (2) NULL ,
[RS5] [smallint] NULL ,
[Test5HighScore] [smallint] NULL ,
[PR5] [int] NULL ,
[Test5HDate] [datetime] NULL ,
[Test5Pass] [smallint] NULL

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-17 : 13:07:14

4 seperate insert statements should do this for you.

insert into students
select --insert all the columns that belong to students here
from main

insert into test
select --insert all the columns that belong to test here
from main

Sorry, but I currently don't have the time to type it all out... hopefully you can use the idea. There really is no need to go through by cursor because each 'main' record holds the FK's and PK's for each table.

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 13:15:17

Before you continue, I'd suggest taking a second pass through your design. First, you don't have any keys defined. Second, you've got transitive dependencies in [test results]. Third, I believe you have partial dependencies in test, as some of the attributes only describe the test, not the (implied) multivalued key of test/student.

<O>

mikejohnson
Posting Yak Master

153 Posts

Posted - 2002-06-17 : 14:09:53

I know I need to make other changes also, I'm just starting w/ this...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources