Help with creating stored procedure 1

[thermolaney]

Hello,
I need to create an append query from a view into a table. I read I should do this as a stored procedure, but get errors parsing the code. Can anyone help me out?

 

[Catadmin]

Are you saying you need to insert the data the query pulls up into a previously existing table?

Is this a one time thing or a constant thing?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[thermolaney]

Yes, I have data in several tables that I need to put into a summary table regularly(quarterly)

 

[Catadmin]

Create a stored procedure that uses an Insert command with the From part of the command being that particular view. That is, if these are completely new records and not updates to existing records.

You can look up Insert in Books Online. It's a relatively easy T-SQL command.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[thermolaney]

I have tried that here is my query

Insert Into dbo.wpindex.sysctrlid, dbo.wpindex.site_id, dbo.wpindex.sys_id

Select dbo.404_wp_cre.sysctrl_id, dbo.404_wp_cre.site_id, dbo.404_wp_cre.SysID

From dbo.404_wp_cre

I get a 170 error.
Thanks

 

[Catadmin]

Your syntax is wrong. The insert command works like this:

Insert <tablename> (column1, column2, column3...)
(Select column1, column2, column3.. from <view/2ndtable>)

Or

Insert <tablename> (ColumnA, columnB, columnC...)
Values (literalValue1, literalValue2, literalValue3...)


BTW, the INTO keyword is optional. And the Where clause (and other predicates) can be used as needed.

The command you would use is:

Insert Into WPINDEX (sysctrlid, site_ID, Sys_ID)
(Select sysctrl_id, site_id, sysID from 404_WP_CRE)

Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[thermolaney]

Thanks a lot,
I got it working. You are the best