question

NbleSavage avatar image
NbleSavage asked

Create a new table and insert / update it in one procedure?

I'm sure the likely answer will be "Use dynamic SQL" but I'm hopeful that there is a more elegant solution.

Within a stored procedure, I'd like to:

1) Create a new table

-- and then

2) Insert records into this same table

-- and lastly

3) Update certain values in this same table.

When SQL compiles my sproc, it tells me that my column names are invalid - because, well, I've got insert and update statements which reference a table that has not been created yet.

What, in your opinion, is the most simple and direct way to accomplish this task?

Thanks all.

stored-proceduresexisting-table
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you update your question to include a bit of background as to why you need to do that? As it seems a pretty weird thing to do in a stored procedure, unless you're creating a temp table? Maybe there is a better way that we can help you with if you give us the bigger picture...
2 Likes 2 ·
Okay, Matt. +1 for thinking beyond the specific answer. As I was providing my own answer, I kept wondering why this would be necessary, but I just ignored those thoughts. ;)
0 Likes 0 ·
I'm right on board there. Why do all three. It sounds like a logical breakdown. I see this all the time, create a temp table, insert it with data, then update that data, then select the temp table. Instead, a good select statement with appropriate joins elimniates all the other processing.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

What version of SQL Server are you using? the following code compiled and ran for me in SQL Server 2008.

CREATE PROCEDURE create_insert_update
AS
BEGIN
    CREATE TABLE new_table
    (
    	id int,
    	something varchar(50)
    )

    INSERT INTO new_table
    	(id, something)
    	VALUES (1, 'this is something')

    UPDATE new_table
    SET something = 'something new'
    WHERE id = 1
END

There was a situation once where I had a similar problem, but I believe it was Intellisense-related and didn't affect compiling. In that case, I cheated by doing the following:

  1. create the table
  2. create and compile the stored procedure
  3. drop the table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NbleSavage avatar image
NbleSavage answered

I'm running SQL Server 2008.

I've cheated in the same way you described in the 2nd part of your response :)

I'm hoping to come upon the best approach, since this condition happens with some frequency in my work.

Peace.

  • Savage
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.