Need your advice on how to combine 2 INSERT to one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need your advice on how to combine 2 INSERT to one

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-30 : 12:13:39

[code]Hi,

I need to import about 20 Excel files into 30 different tables and I need your advices/suggestions what is the best efficient ways to
to these. Below is just an example what I had so far but I don't think it's a efficient way. I am thinking using SSIS but my experience
with SSIS is limited. I have 2 questions.

1. Can I combine step1 and step2 into a single INSERT statement.
2. Does anyone had done similiar project like this before and mind to share some info or know good SSIS web site can help me to accomplish these.

I had looked into the SSIS web site below.
http://www.sqlis.com/sqlis/

Thank you in advance.

Happy New Year to all,

IF OBJECT_ID('StagingCustLoan', 'u') IS NOT NULL
DROP TABLE StagingCustLoan
GO

CREATE TABLE StagingCustLoan
(
CustLoanNum VARCHAR(8) NOT NULL,
CustContactName VARCHAR(20) NULL,
ContactId INT NULL,
ContactTitle VARCHAR(20) NULL,
BankName VARCHAR(20) NULL,
Address VARCHAR(25) NULL,
PhoneNum VARCHAR(12) NULL
)
GO

INSERT StagingCustLoan VALUES ('120606', 'Peter Smith', 12345, 'Manager', 'BofA', '12345 Brookhurst st', '562-598-8942'),
('120606', 'John2 Doe', 89641, 'Supervisor', 'BofA', '12345 Brookhurst st', '562-452-7219'),
('120606', 'User2 Lee', 56173, 'Lead', 'BofA', '12345 Brookhurst st', '562-452-6347'),
('896475', 'Lisa Tran', 87451, 'Director', 'Chase', '45678 Westminster blvd', '310-794-3486'),
('896475', 'Bill Smith', 64757, 'VP', 'Chase', '45678 Westminster blvd', '310-872-3945');
GO

IF OBJECT_ID('CustLoan', 'u') IS NOT NULL
DROP TABLE CustLoan
GO
CREATE TABLE CustLoan
(
LoanNum VARCHAR(10) NOT NULL,
LoanId INT IDENTITY (1,1) NOT NULL,
BankName VARCHAR(30) NULL,
CusAddress VARCHAR(30) NULL,
UpdateBy VARCHAR(20) NULL,
InsertDt DATE NULL
)
GO

IF OBJECT_ID('Contact', 'u') IS NOT NULL
DROP TABLE Contact
GO
CREATE TABLE Contact
(
LoanNum VARCHAR(8) NOT NULL,
ContactId INT NOT NULL,
CustContactName VARCHAR(20) NULL,
ContactTitle VARCHAR(20) NULL,
PhoneNum VARCHAR(12) NULL,
UpdateBy VARCHAR(10) NULL,
InsertDt DATE NULL
)
GO

SELECT *
FROM StagingCustLoan;
GO

CustLoanNum CustContactName ContactId ContactTitle BankName Address PhoneNum
----------- -------------------- ----------- -------------------- -------------------- ------------------------- ------------
120606 Peter Smith 12345 Manager BofA 12345 Brookhurst st 562-598-8942
120606 John2 Doe 89641 Supervisor BofA 12345 Brookhurst st 562-452-7219
120606 User2 Lee 56173 Lead BofA 12345 Brookhurst st 562-452-6347

896475 Lisa Tran 87451 Director Chase 45678 Westminster blvd 310-794-3486
896475 Bill Smith 64757 VP Chase 45678 Westminster blvd 310-872-3945

SELECT *
FROM CustLoan;
GO

-------------------------------------------------------------------------------

-- Need to combine step1 and step2 into one query.

-- step1.

INSERT CustLoan (LoanNum, BankName, CusAddress, UpdateBy, InsertDt)
SELECT DISTINCT
CustLoanNum
,BankName
,[Address]
,'User1' AS UpdateBy
,GETDATE()
FROM StagingCustLoan;
go

SELECT *
FROM CustLoan;
GO

LoanNum LoanId BankName CusAddress UpdateBy InsertDt
---------- ----------- ------------------------------ ------------------------------ -------------------- ----------
120606 1 BofA 12345 Brookhurst st User1 2010-12-30
896475 2 Chase 45678 Westminster blvd User1 2010-12-30

--step2

INSERT Contact (LoanNum, ContactId, CustContactName, ContactTitle, PhoneNum, UpdateBy, InsertDt)
SELECT DISTINCT
CustLoanNum
,ContactId
,CustContactName
,ContactTitle
,PhoneNum
,'User1' AS UpdateBy
,GETDATE()
FROM StagingCustLoan;
go

SELECT *
FROM Contact;
GO

LoanNum ContactId CustContactName ContactTitle PhoneNum UpdateBy InsertDt
-------- ----------- -------------------- -------------------- ------------ ---------- ----------
120606 12345 Peter Smith Manager 562-598-8942 User1 2010-12-30
120606 56173 User2 Lee Lead 562-452-6347 User1 2010-12-30
120606 89641 John2 Doe Supervisor 562-452-7219 User1 2010-12-30

896475 64757 Bill Smith VP 310-872-3945 User1 2010-12-30
896475 87451 Lisa Tran Director 310-794-3486 User1 2010-12-30[/code]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-31 : 08:00:03

Hmm. This "does it", but I am not sure you want this solution. You can create a trigger to fire after insertion into CustLoan to add the related info to Contact as below demonstrates. Probably not the best idea though...



IF OBJECT_ID('StagingCustLoan', 'u') IS NOT NULL
  DROP TABLE StagingCustLoan
GO
IF OBJECT_ID('CustLoan', 'u') IS NOT NULL
  DROP TABLE CustLoan
GO
IF OBJECT_ID('Contact', 'u') IS NOT NULL
  DROP TABLE Contact
GO
CREATE TABLE Contact
(
  LoanNum          VARCHAR(8)   NOT NULL,
  ContactId        INT          NOT NULL,
  CustContactName  VARCHAR(20)  NULL,
  ContactTitle     VARCHAR(20)  NULL,
  PhoneNum         VARCHAR(12)  NULL,
  UpdateBy         VARCHAR(10)  NULL,
  InsertDt         DATETIME         NULL
)
GO


CREATE TABLE StagingCustLoan
(
  CustLoanNum         VARCHAR(8)   NOT NULL,
  CustContactName     VARCHAR(20)  NULL,
  ContactId           INT          NULL,
  ContactTitle        VARCHAR(20)  NULL,
  BankName            VARCHAR(20)  NULL,
  Address             VARCHAR(25)  NULL,
  PhoneNum            VARCHAR(12)  NULL
)
GO




CREATE TABLE CustLoan
(
  LoanNum       VARCHAR(10)   NOT NULL,
  LoanId        INT IDENTITY (1,1) NOT NULL,
  BankName      VARCHAR(30)  NULL,
  CusAddress    VARCHAR(30)  NULL,
  UpdateBy      VARCHAR(20)  NULL,
  InsertDt      DATETIME        NULL
)
GO

 
  --create trigger to insert contact info from staging adter insertion to CustLoan table

Create Trigger tiCustLoan on CustLoan
AFTER INSERT
AS

INSERT Contact (LoanNum, ContactId, CustContactName, ContactTitle, PhoneNum, UpdateBy, InsertDt)
      SELECT DISTINCT
             CustLoanNum
            ,ContactId
            ,CustContactName
            ,ContactTitle
            ,PhoneNum
            ,'User1' AS UpdateBy
            ,GETDATE()
       FROM StagingCustLoan inner join INSERTED on StagingCustLoan.CustLoanNum= INSERTED.LoanNum


GO
INSERT StagingCustLoan VALUES ('120606', 'Peter Smith', 12345, 'Manager', 'BofA', '12345 Brookhurst st', '562-598-8942');
INSERT StagingCustLoan VALUES ('120606', 'John2 Doe', 89641, 'Supervisor', 'BofA', '12345 Brookhurst st', '562-452-7219');
INSERT StagingCustLoan VALUES ('120606', 'User2 Lee', 56173, 'Lead', 'BofA', '12345 Brookhurst st', '562-452-6347');
INSERT StagingCustLoan VALUES('896475', 'Lisa Tran', 87451, 'Director', 'Chase', '45678 Westminster blvd', '310-794-3486');
INSERT StagingCustLoan VALUES('896475', 'Bill Smith', 64757, 'VP', 'Chase', '45678 Westminster blvd', '310-872-3945');
GO
-------------------------------------------------------------------------------

-- Need to combine step1 and step2 into one query.

-- step1.

      INSERT CustLoan (LoanNum, BankName, CusAddress, UpdateBy, InsertDt)
      SELECT DISTINCT
             CustLoanNum
            ,BankName
            ,[Address]
            ,'User1' AS UpdateBy
            ,GETDATE()
       FROM StagingCustLoan;
      go
      
 
      

--step2

       SELECT *
       FROM CustLoan;
      GO
      go
      
      SELECT *
        FROM Contact;
      GO

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources