New table/view creation using insert

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

General SQL Server Forums

New to SQL Server Programming

New table/view creation using insert

Author

Topic

rdonald
Starting Member

2 Posts

Posted - 2012-01-14 : 16:42:39

I am trying to create a new table from an existing table. The problem is that the table I am reading can create 3 new records in the new table. My main file is an invoice file with 3 salesman. I need to create a new file (to be used with crystal) that has a unique row for each salesman on the invoice. I have tired the insert with the select command, but this only allows me to bring in one salesman. I used:
INSERT INTO kenfil
(salesman,invoice)
SELECT slspsn_no,inv_no
from eehdrhst_sql
My problem is that there are slspsn_no_2 and slspsn_no_3 as part of the same row that need to generate their own unique rows.

Thanks

Robert Donald

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-14 : 17:15:55

Couldn't you run the insert statement three times?

INSERT INTO kenfil
  (  salesman, invoice )
SELECT slspsn_no,
       inv_no
FROM   eehdrhst_sql;

-- second row
INSERT INTO kenfil
  (  salesman, invoice )
SELECT slspsn_no_2,
       inv_no
FROM   eehdrhst_sql;

-- third row
INSERT INTO kenfil
  (  salesman, invoice )
SELECT slspsn_no_3,
       inv_no
FROM   eehdrhst_sql;

You could also use the UNPIVOT, something like this, but I would keep it sweet and simple with 3 insert statements:

INSERT INTO kenfil
(  salesman, invoice )
SELECT
	salesman,
	invoice
FROM
	eehdrhst_sql
UNPIVOT
( invoice FOR salesman IN (slspsn_no,slspsn_no_2,slspsn_no_3)) U

rdonald
Starting Member

2 Posts

Posted - 2012-01-14 : 17:24:50

Thanks. I did not realize that it would append to the flle. This just may work.

Robert Donald

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources