OPENROWSET WITH 2 TABLES - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

OPENROWSET WITH 2 TABLES

Author

Topic

bhushanhegde
Starting Member

14 Posts

Posted - 2008-12-02 : 02:14:00

Hi
Check the following code. That inserts data from one text file to one table.

INSERT INTO temp_dept
(dept_id,dept_name,DEPT_GRADE,DEPT_LOC,
DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,
ACTIVE_FLAG,SCHEDULEID)
SELECT dept_id,lower(dept_name),DEPT_GRADE,DEPT_LOC,
DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,
ACTIVE_FLAG,SCHEDULEID FROM OPENROWSET
(BULK N'D:\bcp\temp.txt',
FORMATFILE='D:\bcp\test\ord.fmt',
CODEPAGE ='ACP',
FIRSTROW = 0,
LASTROW = 0,
ERRORFILE = 'D:\bcp\test\error.txt',
MAXERRORS = 100,
ROWS_PER_BATCH = 100)
as document
where dept_id >'0'

Same thing is possible with 2 tables and 2 file or 2 files with one table like different combination?

bhushan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:34:24

yes its possible something like

INSERT INTO temp_dept
(dept_id,dept_name,DEPT_GRADE,DEPT_LOC,
DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,
ACTIVE_FLAG,SCHEDULEID)
SELECT
FROM
( 
SELECT dept_id,lower(dept_name),DEPT_GRADE,DEPT_LOC,
DEPT_HEAD,CHANGE_DT,CHANGE_TYPE,CHANGE_BY,
ACTIVE_FLAG,SCHEDULEID FROM OPENROWSET
(BULK N'D:\bcp\temp.txt',
FORMATFILE='D:\bcp\test\ord.fmt',
CODEPAGE ='ACP',
FIRSTROW = 0, 
LASTROW = 0,
ERRORFILE = 'D:\bcp\test\error.txt',
MAXERRORS = 100,
ROWS_PER_BATCH = 100)
)t1
INNER JOIN
(
)t2
SELECT fields... FROM OPENROWSET
(BULK N'D:\bcp\temp2.txt',
FORMATFILE='D:\bcp\test\ord2.fmt',
CODEPAGE ='ACP',
FIRSTROW = 0, 
LASTROW = 0,
ERRORFILE = 'D:\bcp\test\error2.txt',
MAXERRORS = 100,
ROWS_PER_BATCH = 100)
)t2
ON t2.field=t1.field
where t1.dept_id >'0'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources