Data spilt in two tables

Question

Data spilt in two tables

niladri.primalink

SSCommitted

Points: 1751
More actions
February 3, 2014 at 8:26 am

#283116

Hi All,
I have flat file contains 20,000+ records. Column fields something like FName, Lastname, email, Phone, Address1, address2, sortocde, acoountno.
I want to split the records into the two tables contact and ContactAccount.
There is an Identity Column both in contact and ContactAccount tables. At first the contact table will be filled up taking the columns form the flat file. Columns are ContactID,FName, Lastname, email, Phone, Address1, address2
and after that ContactAccount table will be filled up with the mentioned columns ConatctAccountid,ContactID (which will be generated when I insert the data into the contact table) ,sortocde, acoountno.
Can anyone please help me?
Thanks in advance!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

you'll want to look into using the OUTPUT clause so you can capture the new identity values, along with your data, so tyou can isnert/update the second table.

here's just one example of using the output clause:

CREATE TABLE adds(

adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

code VARCHAR(30) )

DECLARE @MyResults TABLE(

ID int,

newcode VARCHAR(30),

oldcode VARCHAR(30) )

Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )

INSERT INTO adds(code)

OUTPUT

INSERTED.adid,

INSERTED.code,

NULL

INTO @MyResults

SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

SELECT 'cadetblue'

declare @MyResults TABLE(ID int,newcode varchar(30),oldcode varchar(30) )

UPDATE dbo.adds

SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )

OUTPUT

INSERTED.adid,

INSERTED.code,

DELETED.code

INTO @MyResults

WHERE LEFT(code,1) = 'a'

SELECT * FROM @MyResults

--results of update

ID newcode oldcode

1 ALICEB aliceblue

2 ANTIQUEWH antiquewhite

3 AQ aqua*

4 AQ aqua*

5 AQUAMAR aquamarine

6 AZ azure

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi,

You can simply achieve your goal by using the SSIS components. Once you establish a flat file source/connection you need to put a Multicast component and join two destinations to it. If the destinations are tables in sql server (OLE DB destinations), than you can add identity columns to them and choose which of the flat file's columns to include in each of them.

Regards,

Igor

Igor Micev,My blog: www.igormicev.com