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?
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.
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
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)
Thanks alot Chriz.. it was very helpful
Charlotte