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)
 Temp Table To Real Tables

Author  Topic 

Nick
Posting Yak Master

155 Posts

Posted - 2002-12-23 : 13:52:02
Hello-

I need help writing a sproc that will take data from a temp table and putting it into real tables. Here is an example.

Say I my temp table has two columns (Name, City). I want the sproc to check and see if the City alredy exists in a City table. If it does I want it to get the CityID column from that row and place it along with the name in the Name table. If the City does not exist, I want it to be inserted into the City table and both the Name and the new CityID put into the Name table.

Can someone give me some help?

Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-23 : 14:24:39
CAUTION! This script creates non-temp as well as temp tables.
Please use on a test database!

 
CREATE TABLE #MyTemp([NAME] VARCHAR(50), City VARCHAR(50))

CREATE TABLE City(CityID INT IDENTITY (1, 1) NOT NULL, City VARCHAR(50))
CREATE TABLE Names([Name] VARCHAR(50), CityID INT)

INSERT INTO City(City) VALUES('LA')
INSERT INTO City(City) VALUES('New York')
INSERT INTO City(City) VALUES('Dallas')

INSERT INTO #MyTemp([Name], City) VALUES('Graz', 'LA')
INSERT INTO #MyTemp([Name], City) VALUES('Nick', 'New York')
INSERT INTO #MyTemp([Name], City) VALUES('Mike', 'Dallas')
INSERT INTO #MyTemp([Name], City) VALUES('Yak', 'Middle of NoWhereVille')

INSERT INTO Names([Name], CityID)
SELECT mt.[Name], c.CityID
FROM #MyTemp mt
INNER JOIN City c ON c.City = mt.City


--I'm assuming CityID is automatically generated by a IDENTITY or UUID
INSERT INTO City(City)
SELECT City
FROM #MyTemp mt
WHERE NOT EXISTS(Select c.City FROM City c WHERE mt.City = c.City)

select * from city
select * from Names

DROP TABLE City
DROP TABLE Names
DROP TABLE #MyTemp



Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Nick
Posting Yak Master

155 Posts

Posted - 2002-12-23 : 15:38:10
Why are you inserting the Names first and the cities that aren't already present 2nd? Shouldn't it be the other way around? Just wondering.

quote:

INSERT INTO Names([Name], CityID)
SELECT mt.[Name], c.CityID
FROM #MyTemp mt
INNER JOIN City c ON c.City = mt.City

--I'm assuming CityID is automatically generated by a IDENTITY or UUID
INSERT INTO City(City)
SELECT City
FROM #MyTemp mt
WHERE NOT EXISTS(Select c.City FROM City c WHERE mt.City = c.City)
[/code]



Go to Top of Page

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-23 : 15:41:31
Well, I thought I was doing exactly what you were asking, but I must haev missed something. The concepts are there to do what you need. You can probaly figure out exactly what you need from my sample.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Nick
Posting Yak Master

155 Posts

Posted - 2003-01-06 : 13:10:30
As a sort of follow up to this question. How would I handle two tables that have an n-n relationship.

For the sake of argument what if I wanted to put the cityID and corresponding nameID into a third table? How would I go about doing this?



Go to Top of Page

Nick
Posting Yak Master

155 Posts

Posted - 2003-01-06 : 14:42:17
OK-

Let me make this a little more clear. Let's say I have four tables.

CREATE TABLE [dbo].[tblNotes] (
[noteID] [int] IDENTITY (10001, 1) NOT NULL ,
[note] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[tblSegments] (
[segmentID] [int] IDENTITY (10001, 1) NOT NULL ,
[cityName] [nvarchar] (50) NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[tblSegmentsNotes] (
[segmentID] [int] NOT NULL ,
[noteID] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tmpExcelRoute] (
[city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[notes] [nvarchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Now I want to take the records from the tmpExcelRoute table and import them into my other three tables. The city filed will go in the cityName field of the Segments table. The notes filed will go in the note field of tblNotes, and then the ID of the segment and ID of the note will need to go into the the tblSegmentsNotes.

How would I pull this off?

Go to Top of Page
   

- Advertisement -