fill a new table with data acquired using a SELECT statement 2

[diaglez]

Hi, I need to fill a new table with data acquired using a SELECT statement

e.g.

MS SQL SERVER

1** I create the table:
--------inicio--------
Create Table TACCESOS2
(
CODIGO INT,
NOMBRE NVARCHAR(20),
FECHA NVARCHAR(20),
HORA NVARCHAR(20),
TERMINAL SMALLINT
);
---------fin----------

2** I want to insert results of this statement in the new table:

SELECT PECODI AS CODIGO, PENOMB AS NOMBRE, SUBSTRING(CAST(FIFECH AS NVARCHAR),1,11) AS FECHA, CAST(FIHORA AS NVARCHAR) AS HORA, FINODO AS TERMINAL
FROM FFICHA,FPERSO
WHERE SUBSTRING(CAST(FFICHA.FICODI AS NVARCHAR),2,11) = FPERSO.PECODI

but I don´t know how to do it

Thanks

 

[bborissov]

INSERT INTO TACCESOS2 (CODIGO,NOMBRE, FECHA,HORA,TERMINAL)
SELECT PECODI AS CODIGO,
       PENOMB AS NOMBRE,
       SUBSTRING(CAST(FIFECH AS NVARCHAR),1,11) AS FECHA,
       CAST(FIHORA AS NVARCHAR) AS HORA, FINODO AS TERMINAL
FROM FFICHA,FPERSO
WHERE SUBSTRING(CAST(FFICHA.FICODI AS NVARCHAR),2,11) =
      FPERSO.PECODI

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mit99mh]

Try

INSERT INTO TACCESOS2
( CODIGO, NOMBRE, FECHA, HORA, TERMINAL )
SELECT PECODI AS CODIGO, PENOMB AS NOMBRE, SUBSTRING(CAST(FIFECH AS NVARCHAR),1,11) AS FECHA, CAST(FIHORA AS NVARCHAR) AS HORA, FINODO AS TERMINAL
FROM FFICHA,FPERSO
WHERE SUBSTRING(CAST(FFICHA.FICODI AS NVARCHAR),2,11) = FPERSO.PECODI


Hope this helps

 

[mit99mh]

sorry Borislav we posted at the same time!!

 

[bborissov]

)
Why sorry? The main goal of this forum is to help )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[diaglez]

I already done it but I got this error message:
------------
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
------------

Thanks a lot

 

[mit99mh]

Are your datatypes identical?

Open up the table designer for the tables:

TACCESOS2
FFICHA
FPERSO

and check

the columns:

CODIGO INT,
NOMBRE NVARCHAR(20),
FECHA NVARCHAR(20),
HORA NVARCHAR(20),
TERMINAL SMALLINT

match those in the tables you are SELECTing data from.

Cheers

 

[bborissov]

Always set length of varchar or nvarchar types. I can't remember how is the default length.
Try this:
[/code]
INSERT INTO TACCESOS2 (CODIGO,NOMBRE, FECHA,HORA,TERMINAL)
SELECT PECODI AS CODIGO,
PENOMB AS NOMBRE,
SUBSTRING(CAST(FIFECH AS NVARCHAR(200)),1,11) AS FECHA,
CAST(FIHORA AS NVARCHAR(200)) AS HORA,
FINODO AS TERMINAL
FROM FFICHA,FPERSO
WHERE SUBSTRING(CAST(FFICHA.FICODI AS NVARCHAR(200)),2,11) =
FPERSO.PECODI
[/code]


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.