asked on

sql server insert into

I have the following query, it returns the from dates, works great.

WITH Ordered
AS ( SELECT 
               twt.ClientCode,
			   twt.Word ,
			    
             
              
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition ASC
                                ) AS RNF ,
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition DESC
                                ) AS RNL
     FROM     Dictionary.[dbo].[TblWords] twt LEFT JOIN Dictionary.[dbo].[TblWords] tw2 ON twt.ClientCodeWordPosition=tw2.ClientCodeWordPosition
              LEFT JOIN Dictionary.[dbo].[TblWords] tw ON twt.ClientCodeWordPosition = tw.ClientCodeWordPosition
      WHERE ISNUMERIC(twt.word)=1 AND LEN(tw2.word)=4 AND TW2.StrFull like '%[1-2][901][0-9][0-9] to [1-2][901][0-9][0-9]%' AND tw2.Word LIKE '[1-2][901][0-9][0-9]'
   )
SELECT *
FROM   Ordered O
WHERE  O.RNF = 1;

Open in new window

I would like to insert the date into another table

INSERT INTO TblDataExtraction (Clientcode, DatefromExtract)

Open in new window

The client code must be

twt.ClientCode+'datefrom'+twt.word

Open in new window

The datefrom extract is from field twt.word

i dont need to put rnf or rnl into the other table

I dont know how to do the insert when using this query structure.

ASKER CERTIFIED SOLUTION

yo_bee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

I have this:

WITH Ordered
AS ( SELECT 
               twt.ClientCode,
			   twt.Word ,
			    
             
              
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition ASC
                                ) AS RNF ,
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition DESC
                                ) AS RNL
     FROM     Dictionary.[dbo].[TblWords] twt LEFT JOIN Dictionary.[dbo].[TblWords] tw2 ON twt.ClientCodeWordPosition=tw2.ClientCodeWordPosition
              LEFT JOIN Dictionary.[dbo].[TblWords] tw ON twt.ClientCodeWordPosition = tw.ClientCodeWordPosition
      WHERE ISNUMERIC(twt.word)=1 AND LEN(tw2.word)=4 AND TW2.StrFull like '%[1-2][901][0-9][0-9] to [1-2][901][0-9][0-9]%' AND tw2.Word LIKE '[1-2][901][0-9][0-9]'
   )
SELECT ClientCode, (Clientcode + 'datefrom' + Word) as DatefromExtract into TblDataExtraction
FROM   Ordered O
WHERE  O.RNF = 1;

Open in new window

but i get error:
Msg 2714, Level 16, State 6, Line 122
There is already an object named 'TblDataExtraction' in the database.

Pawan Kumar

Pls try

;WITH Ordered
AS ( SELECT
twt.ClientCode,
twt.Word ,

ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
ORDER BY tw.WordPosition ASC
) AS RNF ,
ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
ORDER BY tw.WordPosition DESC
) AS RNL
FROM Dictionary.[dbo].[TblWords] twt LEFT JOIN Dictionary.[dbo].[TblWords] tw2 ON twt.ClientCodeWordPosition=tw2.ClientCodeWordPosition
LEFT JOIN Dictionary.[dbo].[TblWords] tw ON twt.ClientCodeWordPosition = tw.ClientCodeWordPosition
WHERE ISNUMERIC(twt.word)=1 AND LEN(tw2.word)=4 AND TW2.StrFull like '%[1-2][901][0-9][0-9] to [1-2][901][0-9][0-9]%' AND tw2.Word LIKE '[1-2][901][0-9][0-9]'
)
INSERT INTO TblDataExtraction(ClientCode,DatefromExtract)
SELECT ClientCode, (Clientcode + 'datefrom' + Word) as DatefromExtract
FROM Ordered O
WHERE O.RNF = 1;

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

thank you