0

In my Sql server there is one database Employee which has table parish. and another one database with customer which has table clients and fips.There is one stored procedure which take data from this two tables clients and fips . now i want to revert the operation. Take data from parish table and insert into clients and fips. Below is the stored procedure which take data from clients and fips.

ALTER PROCEDURE [dbo].[Rpt_getexportparish] @clientID              AS INT,
                                            @assessment_type       AS NVARCHAR(10),
                                            @political_subDivision AS NVARCHAR(10),
                                            @district              AS NVARCHAR(10),
                                            @acct_status           AS NVARCHAR(10),
                                            @millage_type          AS NVARCHAR(10),
                                            @tax_year              AS NVARCHAR(10)
AS
  BEGIN
      SELECT FIPScode             AS fips_code,
             f.cnty_name          AS gov_name,
             c.NAME               AS gov_agency,
             PhysicalAddress1     AS address2,
             PhysicalAddress2     AS address1,
             TaxYear              AS tax_year,
             PhysicalAddressCity  AS city,
             PhysicalAddressState AS state,
             PhysicalAddressZip   AS zip,
             AssessorName         AS assr_name
      FROM   Clients c
             JOIN fips f
               ON c.FIPScode = f.cnty_fips
      WHERE  id = @clientID
             AND place_fips = @political_subDivision
  END

i want reverse of above. select data from parish and insert into clients and fips table. then what is the sql query for that.

There is no relation in both table clients and fips.

sbm6070
  • 45
  • 2
  • 7
  • 1
    Possible duplicate of [SQL Server: Is it possible to insert into two tables at the same time?](http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time) – JohnLBevan Jan 21 '17 at 10:58
  • why don't you just make two separate insert queries or do you want to do this on a regular basis? – Karan Shah Jan 21 '17 at 11:35
  • Can you specify which source fields are being inserted into which target fields? – pacreely Jan 21 '17 at 13:50
  • 'gov_name' of "parish" go in 'cnty_name' of "fips" table. 'gov_agency' of "parish" go in 'Name' of "Clients" table. 'PhysicalAddress1' of "parish" go in 'address2' of "Clients" table. 'PhysicalAddress2 of "parish" go in 'address1' of "Clients" table. 'TaxYear' of "parish" go in 'tax_year' of "Clients" table. 'PhysicalAddressCity' of "parish" go in 'city' of "Clients" table. 'PhysicalAddressState' of "parish" go in 'state' of "Clients" table. 'PhysicalAddressZip' of "parish" go in 'zip' of "Clients" table. 'AssessorName' of 'parish' go in 'assr_name' of "Clients" table. – sbm6070 Jan 21 '17 at 14:34
  • The [parish] table isn't part of the Select statement. Can you please clarify your table structures & data source/targets within your Question? The comments section is difficult to understand because of the poor layout. – pacreely Jan 21 '17 at 15:05
  • we need to select any nine fields from parish table and insert into clients table. And select other one fields from parish table which are different from nine fields which are inserted in clients table. And insert that one field in fips table. – sbm6070 Jan 21 '17 at 15:15

1 Answers1

0

The OUTPUT command will allow you to insert into a secondary Table with a single query.

Here's an example of how it works

DROP TABLE #SOURCE
DROP TABLE #TARGET1
DROP TABLE #TARGET2

CREATE TABLE #SOURCE(ID INT IDENTITY(1,1),Val FLOAT)
GO
CREATE TABLE #TARGET1(ID INT,Val FLOAT)
GO
CREATE TABLE #TARGET2(ID INT,Val FLOAT)
GO
--ADD 10 ROWS TO SOURCE TABLE
INSERT INTO #SOURCE VALUES (RAND())
GO 10

INSERT INTO #TARGET1
OUTPUT INSERTED.* INTO #TARGET2
SELECT
    *
FROM
    #SOURCE
pacreely
  • 1,881
  • 2
  • 10
  • 16
  • but we insert 4 columns from #SOURCE TO #TARGET1 and 2 columns from #SOURCE TO #TARGET2 where column name also different in both taables #TABLE1 and #TABLE2 – sbm6070 Jan 21 '17 at 13:46