Insert into table with select statement

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)

Insert into table with select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-22 : 08:26:49

Himadrish writes "I JUST WANT TO INSERT FIELD VALUES IN A NEW CREATED TABLE, THE FIELD VALUE WILL BE COMMING FROM SELECT STAMENTS.

NOW, I WANT TO PUT IT ON A PROCEDURE, AND WILL CALL THE STORED PROCEDURE TIME TO TIME. THE SQL STATEMENTS IS CORRECT WHEN EXECUTING IN SQL-ANALYZER, BUT IN PROCEDURE, THIS GIVE ME ERROR.

WHEN I CLICK ON "CHECK SYNTAX' IT SHOWS THE OK, BUT WHEN CLICK ON "APPLY", IT GIVE ME ERROR "THERE IS ALLREADY AN OBJECT NAME<tablename> IN THE DATABASES".

I JUST CONFUSED, WHATS THE MATTER WHETHER TABLE EXIST OR NOT, I ALLREADY TOLD IF THE TABLE EXIST, THEN DELETE IT.

PLS HAVE A LOOK ON THE STORED PROCEDURE, AND LET ME KNOW, IF YOU ALL EXPERTS HAVE ANY CLUE:

THANKS IN ADVANCE TO ALL OF YOU

====================PROCEDURE==================
CREATE PROCEDURE get_Fact_Acc_Quote_Link
AS
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'Fact_Acc_Quote_Link' AND type = 'U')
DROP TABLE Fact_Acc_Quote_Link
GO
CREATE TABLE Fact_Acc_Quote_Link
(
AcctID decimal(9),
Company char(32),
City char(32),
Country char(32),
QuoteID decimal(9),
TaxAmt decimal(9),
ItemId decimal(9),
Quantity decimal(9),
Product char(32),
UPrice decimal(9),
PartNum char(32),
Taxable char(32)
)
GO

INSERT INTO Fact_Acc_Quote_Link (AcctID,Company,City,Country,QuoteID,TaxAmt,ItemId,Quantity,Product,UPrice,PartNum,Taxable)
SELECT Account.AcctID AS AcctID, Account.Company AS Company, Account.city AS City,
Account.Country AS Country, Quotes.QuoteID AS QuoteID,Quotes.TaxAmt AS TaxAmt,
LineItem.ItemId ItemId, LineItem.Quantity AS Quantity, LineItem.Product AS Product,
LineItem.UPrice AS UPrice, LineItem.PartNum AS PartNum, LineItem.Taxable AS Taxable
FROM Account, Quotes, LineItem, TRLNK002,TRLNK018
WHERE Account.Status='Customer'
AND Quotes.OrdType='Order'
AND TRLNK002.Account=Account.AcctID
AND TRLNK002.QUOTES=TRLNK018.QUOTES
AND TRLNK018.LINEITEM=LineItem.ItemId
GO"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-22 : 08:28:30

YOU CAN'T PUT GO INTO A STORED PROCEDURE. IT IS NOT A COMMAND BUT A BATCH TERMINATOR. SQL SERVER WILL NOT COMPILE ANY CODE IN A PROCEDURE AFTER GO. AND STOP USING ALL CAPS BECAUSE IT IS UNBELIEVEABLY ANNOYING AND IMPOLITE.

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-22 : 10:49:18

Rough Monday Rob?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources