New Table 1

[codrutza]

Hi. I'm trying to build a table for a report.
USE [Data003]
GO
/****** Object: StoredProcedure [dbo].[Z_CodrCarriers] Script Date: 10/30/2012 12:23:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;

SELECT Suppliers.Code as SupCode
INTO dbo.CodrCarriers_T
FROM Suppliers
WHERE Suppliers.Code LIKE 'S%'

I don't know if I wrote right, but the table CodrCarriers_T
doesn't appear among the other tables created by programmers.
What do I do wrong, please?

 

[dhulbert]

Tables don't just appear in the table list in Management Studio, you need to right click and select refresh.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[codrutza]

Thanks Douglas for your reply. I press F5, and I did refresh like you said, and still doesn't appear.

 

[SQLBill]

This might sound foolish...but...did you execute the stored procedure?

EXEC [dbo].[Z_CodrCarriers]

Just running the script you provided only changes the existing stored procedure, it doesn't actually run it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[IanWaterman]

Have you actaully created the table?

This will need a separate piece of code

Create table CodrCarriers_T
( SupCode Int );

if your SupCode is a string change to something like NVarchar(50) dependiong on max length of SupCode string.

Ian

 

[codrutza]

Nothing is foolish as me , so I appreciate any advice I get.
I will try again when I'll be back to work.

 

[SQLBill]

Ian....

a SELECT INTO creates the table using the values being selected.

This issue looks more likely to be it didn't get run.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

codrutza...

I forgot to mention one issue with your stored procedure. After you run it for the first time, it will create the table, but then when it is run again the proc will fail since the table exists.

You have a few choices....

1. have the procedure drop the table if it exists.
2. change the SELECT ... INTO to be an INSERT INTO. This would require the table to already be created.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

To add to SQLBill's #2 you might need a TRUNCATE TABLE or DELETE FROM if you want a clean table.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[codrutza]

I have now:
USE [Data003]
GO
/****** Object: StoredProcedure [dbo].[Z_CodrCarriers] Script Date: 11/01/2012 12:53:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;

drop table [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] [VARCHAR(25)], [SupCode] [VARCHAR(25)])

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.

FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')

EXEC [dbo].[Z_CodrCarriers] 

SELECT SupAcc, SupCode
FROM CodrCarriers_T

Doesn't give me any error, but the table is nowhere, and it didn't bring me any records :(

 

[SQLBill]

Again....it's about running the stored procedure. If your stored procedure is exactly like you posted, then the execute and select are part of the stored procedure and won't be run until you execute the stored procedure. The problem with that is the procedure will enter a loop since you are calling it within the stored procedure itself. Try this:

USE [Data003]
GO
--first we will drop the existing procedure so we can start clean
DROP PROCEDURE [dbo].[Z_CodrCarriers]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--now we will create the stored procedure
CREATE PROCEDURE [dbo].[Z_CodrCarriers] 
AS

SET NOCOUNT ON;

drop table [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] [VARCHAR(25)], [SupCode] [VARCHAR(25)])

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.[Code]
FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')

--we are done with the stored procedure and need to 'close' it
GO
--now we execute (run) the stored procedure
EXEC [dbo].[Z_CodrCarriers] 

--now we gather the data from the stored procedure.
SELECT SupAcc, SupCode
FROM CodrCarriers_T

Anytime you need to run the stored procedure, you run the EXEC statement. Then you run the SELECT to find out what is in the table.

Let us know how this works.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

You might also check if the table exists before you do a "DROP TABLE", as it will through an error if it does not exist.
See BOL for "IF OBJECT_ID".

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLBill]

Djj....thanks for pointing that out. I meant to include that in my code.

Codrutza,

Change this:

drop table [dbo].[CodrCarriers_T]

to be this:

--Check if table exists, if so, drop it
IF OBJECT_ID('Data003.[dbo].[CodrCarriers_T] IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[codrutza]

I have changed the type of [SupAcc] CHAR(9), [SupCode] CHAR(10) to be like in the original table
And I wrote:

USE [Data003]
GO
--first we will drop the existing procedure so we can start clean
DROP PROCEDURE [dbo].[Z_CodrCarriers]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--now we will create the stored procedure
CREATE PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;

--drop table [dbo].[CodrCarriers_T]
--Check if table exists, if so, drop it
IF OBJECT_ID([Data003].[dbo].[CodrCarriers_T]) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] CHAR(9), [SupCode] CHAR(10))

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.

FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')

--we are done with the stored procedure and need to 'close' it
GO
--now we execute (run) the stored procedure
EXEC [dbo].[Z_CodrCarriers] 

--now we gather the data from the stored procedure.
SELECT SupAcc, SupCode
FROM CodrCarriers_T 

And I get:
Msg 3701, Level 11, State 5, Line 2
Cannot drop the procedure 'dbo.Z_CodrCarriers', because it does not exist or you do not have permission.
Msg 4104, Level 16, State 1, Procedure Z_CodrCarriers, Line 9
The multi-part identifier "Data003.dbo.CodrCarriers_T" could not be bound.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.Z_CodrCarriers'.
Msg 208, Level 16, State 1, Line 5
Invalid object name 'CodrCarriers_T'.

 

[SQLBill]

The first error:
Msg 3701, Level 11, State 5, Line 2
Cannot drop the procedure 'dbo.Z_CodrCarriers', because it does not exist or you do not have permission.

normally tells us the stored procedure was never created. But you said you created it at the beginning of this thread. Maybe you were throwing us off with the ALTER statement. ALTER means the stored procedure already exists and you are making changes to it. Is it possible the stored procedure never existed? If so, comment out the DROP PROCEDURE line in the script I provided you....you never CREATEd the stored procedure so there isn't one to drop.

Let us know how that works.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[codrutza]

Sorry

I write now this:

USE [Data003]
GO
--first we will drop the existing procedure so we can start clean
--DROP PROCEDURE [dbo].[Z_CodrCarriers]
--GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--now we will create the stored procedure
CREATE PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;

--drop table [dbo].[CodrCarriers_T]
--Check if table exists, if so, drop it
IF OBJECT_ID([Data003].[dbo].[CodrCarriers_T]) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] CHAR(9), [SupCode] CHAR(10))

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.

FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')

--we are done with the stored procedure and need to 'close' it
GO
--now we execute (run) the stored procedure
EXEC [dbo].[Z_CodrCarriers] 

--now we gather the data from the stored procedure.
SELECT SupAcc, SupCode
FROM CodrCarriers_T

And I get:

Msg 4104, Level 16, State 1, Procedure Z_CodrCarriers, Line 9
The multi-part identifier "Data003.dbo.CodrCarriers_T" could not be bound.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.Z_CodrCarriers'.

 

[SQLBill]

Let's try this change:

--drop table [dbo].[CodrCarriers_T]
--Check if table exists, if so, drop it
IF OBJECT_ID([highlight #FCE94F][dbo].[CodrCarriers_T][/highlight]) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

If you get the error again, double-click on it and let us know which line it highlights.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[codrutza]

USE [Data003]
GO
--first we will drop the existing procedure so we can start clean
--DROP PROCEDURE [dbo].[Z_CodrCarriers]
--GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--now we will create the stored procedure
CREATE PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;


--drop table [dbo].[CodrCarriers_T]
--Check if table exists, if so, drop it
IF OBJECT_ID([dbo].[CodrCarriers_T]) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] CHAR(9), [SupCode] CHAR(15))

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.

FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')

--we are done with the stored procedure and need to 'close' it
GO
--now we execute (run) the stored procedure
EXEC [dbo].[Z_CodrCarriers] 

--now we gather the data from the stored procedure.
SELECT SupAcc, SupCode
FROM CodrCarriers_T

I get: 
Msg 4104, Level 16, State 1, Procedure Z_CodrCarriers, Line 10
The multi-part identifier "dbo.CodrCarriers_T" could not be bound.

IF OBJECT_ID([dbo].[CodrCarriers_T]) IS NOT NULL

Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.Z_CodrCarriers'.

EXEC [dbo].[Z_CodrCarriers] 

Msg 208, Level 16, State 1, Line 5
Invalid object name 'CodrCarriers_T'.

SELECT SupAcc, SupCode

 

[flapeyre]

I think you are trying to call the stored procedure recursively.

[COLOR=#0000FF]USE[/color] [Data003]
GO
[COLOR=#006600]--first we will drop the existing procedure so we can start clean[/color]
[COLOR=#006600]--DROP PROCEDURE [dbo].[Z_CodrCarriers][/color]
[COLOR=#006600]--GO[/color]
[COLOR=#0000FF]SET[/color] ANSI_NULLS [COLOR=#0000FF]OFF[/color]
GO
[COLOR=#0000FF]SET[/color] [COLOR=#0000FF]QUOTED_IDENTIFIER[/color] [COLOR=#0000FF]OFF[/color]
GO
[COLOR=#006600]--now we will create the stored procedure[/color]
[COLOR=#0000FF]CREATE[/color] [COLOR=#0000FF]PROCEDURE[/color] [dbo].[Z_CodrCarriers] 
[COLOR=#0000FF]AS
[highlight #FCE94F]BEGIN[/highlight][/color]

[COLOR=#0000FF]SET[/color] [COLOR=#0000FF]NOCOUNT[/color] ON;


[COLOR=#006600]--drop table [dbo].[CodrCarriers_T][/color]
[COLOR=#006600]--Check if table exists, if so, drop it[/color]
[COLOR=#0000FF]IF[/color] [COLOR=#00B0B0]OBJECT_ID[/color]([dbo].[CodrCarriers_T]) [COLOR=#0000FF]IS[/color] [COLOR=#FF0000]NOT[/color] [COLOR=#FF0000]NULL[/color]
[COLOR=#0000FF]DROP[/color] [COLOR=#0000FF]TABLE[/color] [dbo].[CodrCarriers_T] 

[COLOR=#0000FF]CREATE[/color] [COLOR=#0000FF]TABLE[/color] [dbo].[CodrCarriers_T] ([SupAcc] [COLOR=#0000FF]CHAR[/color]([COLOR=#FF0000]9[/color]), [SupCode] [COLOR=#0000FF]CHAR[/color]([COLOR=#FF0000]15[/color]))

[COLOR=#0000FF]INSERT[/color] [COLOR=#0000FF]INTO[/color] [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
([COLOR=#0000FF]SELECT[/color] Suppliers.AccountNumber, Suppliers.[Code]
[COLOR=#0000FF]FROM[/color] [dbo].[Suppliers]
[COLOR=#0000FF]WHERE[/color] Suppliers.Code [COLOR=#FF0000]LIKE[/color] [COLOR=#808080]'SL%'[/color])

[COLOR=#006600]--we are done with the stored procedure and need to 'close' it[/color]
GO
[highlight #FCE94F]END[/highlight]

Just run the above to create the SP.

THEN you can execute it separately:

[COLOR=#006600]--now we execute (run) the stored procedure[/color]
[COLOR=#0000FF]EXEC[/color] [dbo].[Z_CodrCarriers] 

[COLOR=#006600]--now we gather the data from the stored procedure.[/color]
[COLOR=#0000FF]SELECT[/color] SupAcc, SupCode
[COLOR=#0000FF]FROM[/color] CodrCarriers_T

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[flapeyre]

One other thing I noticed. Is there any particular reason why you are dropping and recreating the table every time you call the SP?

If the table structure never changes, you can use TRUNCATE TABLE instead (of course, the table has to exist first).

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain