Select Into brain F@art - 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

General SQL Server Forums

New to SQL Server Programming

Select Into brain F@art

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:05:07

So i want to take the resultset from this query and drop those into a new table called dbo.TrafficWarrantData. I know i know the syntax for this, or did at one point:


SELECT pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd, 
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID


					UNION
					SELECT 	pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd,
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID
					AND		cfs.OtherAgency = 1


						ORDER BY pd.LastName, pd.FirstName

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-21 : 16:09:56

quote:

Originally posted by WJHamel

So i want to take the resultset from this query and drop those into a new table called dbo.TrafficWarrantData. I know i know the syntax for this, or did at one point:



select * into dbo.TrafficWarrantData from
(
SELECT pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd, 
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID


					UNION
					SELECT 	pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd,
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID
					AND		cfs.OtherAgency = 1

)dt

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:10:26

[code]SELECT * INTO YourNewTable
FROM
(
SELECT pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
FROM CFSData cfs, CombinedDesc cd, PersonData pd,
TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
WHERE cfs.CFSID = pd.FormID
AND cfs.CFSID = tc.CFSID
AND tc.OffenseID = ch.ID
AND tc.CourtID = ct.ID
AND cfs.CallTypeID = cd.ID
AND tc.ActionID = tat.ID
AND pd.PersonID = tat.SubjectID

UNION
SELECT pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
FROM CFSData cfs, CombinedDesc cd, PersonData pd,
TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
WHERE cfs.CFSID = pd.FormID
AND cfs.CFSID = tc.CFSID
AND tc.OffenseID = ch.ID
AND tc.CourtID = ct.ID
AND cfs.CallTypeID = cd.ID
AND tc.ActionID = tat.ID
AND pd.PersonID = tat.SubjectID
AND cfs.OtherAgency = 1
)t

ORDER BY pd.LastName, pd.FirstName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 16:11:35

You do you want to store redundant data? Is there a purpose? Why not just create a view?

And what's with the non ANSI Joins?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-21 : 16:11:36

quote:

Originally posted by visakh16

SELECT * INTO YourNewTable
FROM
(
SELECT pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd, 
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID


					UNION
					SELECT 	pd.LastName, pd.FirstName, pd.MiddleName, pd.Suffix,
							 tc.CitationNumber, ch.GaCodeSec, tc.ID AS CitationID, tat.ActionTaken, tc.WrittenWarning,
							tc.InputDate, cfs.CFSID, cd.ReportCode, cfs.ReceivedTime, ct.Description AS Court
					FROM	CFSData cfs, CombinedDesc cd, PersonData pd,
							TrafficCitations tc, Charges ch, TrafficActionTaken tat, CombinedDesc ct
					WHERE	cfs.CFSID = pd.FormID
					AND		cfs.CFSID = tc.CFSID
					AND		tc.OffenseID = ch.ID
					AND		tc.CourtID = ct.ID
					AND    	cfs.CallTypeID = cd.ID
					AND		tc.ActionID = tat.ID
					AND		pd.PersonID = tat.SubjectID
					AND		cfs.OtherAgency = 1
)t

						ORDER BY pd.LastName, pd.FirstName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Do you think it is ok to keep the ORDER BY?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:12:28

Also that order by doesnt make sense during new table creation so you can dispense with that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-21 : 16:14:03

quote:
Originally posted by visakh16

Also that order by doesnt make sense during new table creation so you can dispense with that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes - and alias pd isn't known at this time I think.

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 16:14:46

quote:
Originally posted by webfred
select * into dbo.TrafficWarrantData from

bad German...redundant data...oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:17:21

Wow. I got inundated QUICK like!!!
Anyway, yes, i know i can drop the order by.
and the whole script was generated by our clients cold fusion app and it was my quickest shortcut to use that query to extract the data into the sql tables we're moving them into.
thank you!

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:18:17

alias pd is the PersonData table referenced.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:21:18

quote:
Originally posted by WJHamel

alias pd is the PersonData table referenced.

yeah true
but thats out of scope when you've that derived table created over query with alias t which is what Fred pointed out

so it should be

ORDER BY t.LastName, t.FirstName
in my suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:24:42

but i don't really need the Order By at all because it's now a select into, true?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-21 : 16:28:44

quote:
Originally posted by X002548

quote:
Originally posted by webfred
select * into dbo.TrafficWarrantData from

bad German...redundant data...oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mea culpa

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:36:25

and also, it's not pulling in duplicate data. you'll see that extra pull on the back end of that union (cfs.Otheragency=1), hanging there like a septic appendix. that's keeping it as it should be.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:39:16

quote:
Originally posted by WJHamel

but i don't really need the Order By at all because it's now a select into, true?

yep...true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-21 : 16:43:53

the more i learn from you guys, the less stressful and the more "fun" my job is, if that's possible.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-21 : 17:49:21