insert the data into two different tables from one table

• KpSQL123

  SSC Veteran

  Points: 253

  More actions

  June 5, 2012 at 5:18 am

  #270244

  In one table i have data like:

  empnoempnameempsalRowNum

  100purush10001

  100purush20002

  101XXXX80001

  102AAAA70001

  103BBBB30001

  104CCCC30001

  104CCCC50002

  104CCCC40003

  Now i want insert this data into two different tables like

  table1:

  empnoempnameempsalRowNum

  100purush10001

  101XXXX80001

  102AAAA70001

  103BBBB30001

  104CCCC30001

  Table 2:

  empnoempnameempsalRowNum

  300purush20002

  301CCCC50002

  302CCCC40003

  Please help me out regarding the same

• vinu512

  SSCoach

  Points: 15729

  More actions

  June 5, 2012 at 5:33 am

  #1496981

  Like This:

  --Creating Tables

  Create table Ex

  (empno int,

  empname varchar(30),

  empsalint,

  Rownum int )

  Create table Ex2

  (empno int,

  empname varchar(30),

  empsalint,

  RowNum int )

  Create table Ex3

  (empno int Identity(300,1), --Create Empno as Identity. Starting from 300 and Incremented by 1

  empname varchar(30),

  empsalint,

  RowNum int )

  --Inserting Sample Data

  Insert Into Ex

  Select 100,'purush',1000,1

  Union ALL

  Select 100,'purush',2000,2

  Union ALL

  Select 101,'XXXX', 8000,1

  Union ALL

  Select 102,'AAAA',7000,1

  Union ALL

  Select 103,'BBBB',3000,1

  Union ALL

  Select 104,'CCCC',3000,1

  Union ALL

  Select 104,'CCCC',5000,2

  Union ALL

  Select 104,'CCCC',4000,3

  --Insert Queries For Your Requirement

  Insert Into Ex2

  Select * From Ex Where Rownum = 1

  Insert Into Ex3

  select empname, empsal, Rownum From Ex Where Rownum > 1

  --Checking Results

  Select * From Ex2

  Select * From Ex3

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• KpSQL123

  SSC Veteran

  Points: 253

  More actions

  June 5, 2012 at 6:05 am

  #1496994

  Thanks for your reply...If i have lakhs of records how can i load

• June 5, 2012 at 6:26 am

  This was removed by the editor as SPAM

• vinu512

  SSCoach

  Points: 15729

  More actions

  June 5, 2012 at 10:18 pm

  #1497388

  KpSQL123 (6/5/2012)

  ---

  Thanks for your reply...If i have lakhs of records how can i load

  Is it a one time Requirement or a Regular thing??....If it is a one time requirement then you can do it with TSQL. But, if it is a regular thing then I would also suggest using SSIS Data Flow Task as mentioned above by Stewart.

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• KpSQL123

  SSC Veteran

  Points: 253

  More actions

  June 6, 2012 at 1:28 am

  #1497436

  It is one time requirement

• vinu512

  SSCoach

  Points: 15729

  More actions

  June 6, 2012 at 3:03 am

  #1497478

  KpSQL123 (6/6/2012)

  ---

  It is one time requirement

  In that case you can use any of the ways.

  If you use TSQL, you would have to make sure to do it at some time in the day when usage and load on the server is less.

  Otherwise you can always use SSIS Data Flow Task. If you know how to do it, then it is always a better option Performance wise.

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉