locked
Stored Procedure in SQL 2012 to get data from 1 database, and insert in into another database table RRS feed

  • Question

  • Hi All, 

    I am totally new to stored procedures in SQL Server, creating only very simple stored procedures.  Im hoping someone can assist me with this.

    I have two seperate Databases, with different tables in.  I wish to get data from several rows from a table in the first database.  Then insert this data to a table in second database.  The tables are not exactly the same, but the fields are with different names.

    Table 1 has a field Status can be "L" or "C" or "B"

    I wish to select all records that are status "C" or "B" from database 1, table 1 and insert them into a different database2, table1. 

    Database1.Table1
    internalid (def value "newid())")
    idno int
    status navarchar
    url navarchar
    [email address] navarchar
    desc navarchar
    date


    Database2.Table1

    internalid (auto populates)
    idno int
    status navarchar
    url navarchar
    email navarchar
    description navarchar
    Date

    Many thanks for any assistance, in pointing me in the correct direction.

    Mark

    Monday, October 7, 2013 10:52 AM

Answers

  • Try the below:

    Insert into Database1.Table2(
    idno ,status ,url ,email ,description ,Date)
    Select 
    idno ,status ,url ,[email address] ,desc ,date 
    from databse1.Table1 Where status in ('C','B')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Harsha Lella Monday, October 7, 2013 7:40 PM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:37 AM
    Monday, October 7, 2013 10:57 AM
  • Hi

    PFB code

    GO Create database Database1 GO Create table Database1.dbo.Table1 ( internalid int identity(1,1), idno int, status nvarchar(20), url nvarchar(30), [email address] nvarchar(300), desc1 nvarchar(100), date datetime) GO Create database Database2 GO Create table Database2.dbo.Table1 ( internalid int identity(1,1), idno int, status nvarchar(20), url nvarchar(30), [email address] nvarchar(300), desc1 nvarchar(100), date datetime) insert into Database1.dbo.Table1(idno ,status ,url ,[email address] ,desc1 ,Date) select '1','c','http://','Sra@sr1.com','desc1',getdate() GO insert into Database2.dbo.Table1(idno ,status ,url ,[email address] ,desc1 ,Date) select idno ,status ,url ,[email address] ,desc1 ,Date from Database1.dbo.Table1

    status in ('C','B')




    • Edited by SaravanaC Monday, October 7, 2013 11:23 AM
    • Proposed as answer by Harsha Lella Monday, October 7, 2013 7:40 PM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:37 AM
    Monday, October 7, 2013 11:16 AM

All replies

  • Try the below:

    Insert into Database1.Table2(
    idno ,status ,url ,email ,description ,Date)
    Select 
    idno ,status ,url ,[email address] ,desc ,date 
    from databse1.Table1 Where status in ('C','B')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Harsha Lella Monday, October 7, 2013 7:40 PM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:37 AM
    Monday, October 7, 2013 10:57 AM
  • Hi

    PFB code

    GO Create database Database1 GO Create table Database1.dbo.Table1 ( internalid int identity(1,1), idno int, status nvarchar(20), url nvarchar(30), [email address] nvarchar(300), desc1 nvarchar(100), date datetime) GO Create database Database2 GO Create table Database2.dbo.Table1 ( internalid int identity(1,1), idno int, status nvarchar(20), url nvarchar(30), [email address] nvarchar(300), desc1 nvarchar(100), date datetime) insert into Database1.dbo.Table1(idno ,status ,url ,[email address] ,desc1 ,Date) select '1','c','http://','Sra@sr1.com','desc1',getdate() GO insert into Database2.dbo.Table1(idno ,status ,url ,[email address] ,desc1 ,Date) select idno ,status ,url ,[email address] ,desc1 ,Date from Database1.dbo.Table1

    status in ('C','B')




    • Edited by SaravanaC Monday, October 7, 2013 11:23 AM
    • Proposed as answer by Harsha Lella Monday, October 7, 2013 7:40 PM
    • Marked as answer by Allen Li - MSFT Wednesday, October 16, 2013 8:37 AM
    Monday, October 7, 2013 11:16 AM