Stored Procedure- Inserting Data from a Query Into a Table

Transact-SQL
1

Hi I'm Charlotte,
I'd like to find out to write a stored procedure. I have created a table with all the required fields. I have a SQL query that pulls specific data from a few tables from a more than one database. The data from this query needs to be inserted into the table I created in a separate database. Can you help me with this?

2

I hope this will help!

CREATE PROCEDURE INSERT_DATA
    AS
    BEGIN
    INSERT INTO DB1.dbo.Table1 (field1,field2)
    SELECT field1,field2 FROM DB2.dbo.Table2
    END
    GO

DB1 --> database1
DB2 --> database2
dbo -->schema

You can save the SQL query as a View and replace db2.dbo.Table2 with db2.dbo.View1.

3

try this

for example your destination table three columns

create procedure update_table
As
Begin
declare @column1 varchar(max),
@column2 varchar(max),
@column3 varchar(max)

declare cuts2 cursor for your_source_query
open cuts2
PRINT 'NOW WE ARE OPEN CURSOR'
fetch next from cuts2 into @column1 ,@column2,@column3
while @@FETCH_STATUS=0
begin
update your_destination_table_name
set destination_table_column1 = @column1,
destination_table_column2 = @column2,
destination_table_column3 = @column3

fetch next from cuts2 into @column1 ,@column2,@column3
end
close cuts2
deallocate cuts2
END

--exec dba.update_table

4

personally I wouldn't use a CURSOR solution:

Absolutely dreadful performance - could easily be 100x slower than a Set Based solution (as Chriz posted above)
Hard to maintain - lots of work if a new column is added, Error prone if the datatype/size/etc of a column changes
Not ATOMic (without considerable effort)

5

Thanks alot Chriz.. it was very helpful
Charlotte