Select from one table, inserting into another
-
Hi All,
I'm new to SQL Server and am trying to move certain columns from a table in one database into another database. I trying this with a cursor.
Q: is a cursor the right way to go about this?
Q: I'm getting an 'Invalid column name' error on the three SET statements. Can you explain why this is?
Thanks
MVP
USE gcTelco
GO
DECLARE @PhoneNumber varchar (30), @PhoneTypeID tinyint, @BridgeID smallint
DECLARE @MyPrimaryKey int
SET @MyPrimaryKey = 0
DECLARE PhoneNumbers_Cursor CURSOR FOR
SELECT p.Number, p.PhoneTypeID, p.BridgeID
FROM Autoconferencing.dbo.Phone p
OPEN PhoneNumbers_Cursor
FETCH NEXT FROM PhoneNumbers_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MyPrimaryKey = @MyPrimaryKey + 1
SET @PhoneNumber = Number
SET @PhoneTypeID = PhoneTypeID
SET @BridgeID = BridgeID
INSERT INTO gcTelco.dbo.gcPhoneNumber VALUES (
@MyPrimaryKey, --PhoneNumberID
@PhoneNumber, --PhoneNumber
0, --TrunkGroupID
@BridgeID, --BridgeID
@PhoneTypeID --PhoneTypeID
)
FETCH NEXT FROM PhoneNumbers_Cursor
END
CLOSE PhoneNumbers_Cursor
DEALLOCATE PhoneNumbers_Cursor
GO
-
Q1: No, a cursor is _very_ seldomly the right way. But without your DDL, it's like a shot in the dark.
Q2: Look at the example given in BOL under DECLARE CURSOR. You're missing the assigment of the SELECT list to the variables.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi
Frank's right - only use a cursor if you have a death wish.
But this should be fairly straight forward. In your new table make colum 1 you PhoneNumberID, and set it as an INTEGER datatype with IDENTITY set to YES
Then, just do a straight INSERT - SELECT
something on the lines of:....
INSERT dbo.gcPhoneNumber
(PhoneNumber, PhoneTypeID, BridgeID)
SELECT PhoneNumber, PhoneTypeID, BridgeID
FROM Autoconferencing.dbo.Phone
Does that help?
-
Computing the median..are the only times to use a cursor in SQL Server.
Umh, I might be wrong, but there is no need for a cursor here anyway (...at least, if you don't mind some proprietary T-SQL extension)
IF OBJECT_ID('median') IS NOT NULL
DROP TABLE median
GO
CREATE TABLE median(
col1 INT
)
GO
INSERT INTO median (col1) VALUES (1)
INSERT INTO median (col1) VALUES (2)
INSERT INTO median (col1) VALUES (3)
INSERT INTO median (col1) VALUES (3)
INSERT INTO median (col1) VALUES (4)
INSERT INTO median (col1) VALUES (7)
INSERT INTO median (col1) VALUES (8)
INSERT INTO median (col1) VALUES (9)
SELECT
((SELECT
MIN(Col1)
FROM
(SELECT TOP 50 PERCENT
Col1
FROM
Median
ORDER BY
Col1
DESC) a) +
(SELECT
MAX(Col1)
FROM
(SELECT TOP 50 PERCENT
Col1
FROM
Median
ORDER BY
Col1) a))/2. AS Median
This can also easily be rewritten to compute the "statistical" median instead of the "financial" one.
Is this case I suspect it to look something like
SELECT
MAX(Col1) AS Median
FROM
(SELECT TOP 50 PERCENT
Col1
FROM
Median
ORDER BY
Col1) a
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I do mind proprietary code ...
Yes, I know. And each time I see this, there is a certain picture coming to my mind. Please don't think I am combative or something like that, but I imagine you visiting China and standing on the Tiananmen Square and telling all passing Chinese to stop using their proprietary language inmediately and rather start using Esperanto right now, so that people in Germany, Brazil or Iceland can easily understand them when those Chinese visit these countries. I am really curious a) how many people would listen b) would change their language because of this argumentation and c) how many people in Germany, Brazil or Iceland will actually understand a Chinese guy asking for the way in Esperanto. To be honest, I guess most Germans (I for myself anyway) do have massive problems when a guy from Bavaria will ask for the way.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi All,
Thanks for the many replies and ... extra info?
My, but cursors do not seem to be very popular. Regarding using
INSERT dbo.gcPhoneNumber
(PhoneNumber, PhoneTypeID, BridgeID)
SELECT PhoneNumber, PhoneTypeID, BridgeID
FROM Autoconferencing.dbo.Phone
How is the Primary Key handled then? My understanding was that the value for the PK had to be specifically created...
Thanks!
MVP
-
Please keep the comments on topic and professional. What may be "nuts" to you, may solve a problem nicely for another.
Marshall, the PK is the unique set of values in a row the identify this row as being different from others. A phone number can potentially be a primary key since it isn't likely issued to more than one person, but the data inherently has a PK (or should). In your script above you are explicitly setting a row number for each row in the cursor, which would be a primary key FOR THAT result set. Each row is uniquely numbered in your scheme. However that does not mean that your data does not contain duplicates somewhere.
-
Hi Steve,
may I say, that while I appreciate you stepping in here (and in the other thread), I think it's not necessary to edit postings. The fora here are pretty much self-regulating, so that there is no need to. And even if there is a posting way off, someone will step in and put the posters head in the right place again.
Just my $0.02 cents
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
False analogy! I have a high school math book from the 1850's that has 6 pages of the weights and measure of the nations of Europe. There is a note at the end of that section which says "The Metric System is becoming popular and might become a European standard sometime in the future."
Ha, that's the stuff "Mühlenberg Legends" are made of.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply