asked on

Sql server insert

I have a query that works perfectly so far. I would like to insert its results into a table can it do that at the same time? Not sure how to do that as here the sql has been built up

The results table is called TblCompareEvents

USE [ClientData]
GO
/****** Object:  StoredProcedure [dbo].[usp_ClientDifferencesLogic]    Script Date: 06/02/2017 09:35:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_ClientDifferencesLogic]
(
	@FieldName sysname,
	@TableName sysname,
	@PKName sysname,
	@VehCat sysname
)
AS

DECLARE @SQL AS VARCHAR(MAX) =''

SET NOCOUNT ON;

SET @SQL = 'SELECT [' +@TableName + '].[' +@PKName +']' + ', + [' +@TableName + '].[BATCH] ,[' +@TableName + ' PREV].['  +@FieldName + '] AS PREV,[' +@TableName + '].['+@FieldName +'] AS CHANGE' + ',
    CASE
            WHEN RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],''''))) THEN ''False''
            ELSE ''True'' 
    END AS Resultdiff, '
    +'[' + @TableName + '].[' +@VehCat + ']' +'
FROM [' + @TableName+ ']'+'
    INNER JOIN [' +@TableName +' PREV] ON [' + @TableName + '].[' +@PKName +'] = ['+@TableName + ' PREV].' + @PKName+'
WHERE RTrim(LTrim(IsNull([' +@TableName +'].[' +@FieldName + '],''''))) <> RTrim(LTrim(IsNull([' +@TableName +' PREV].[' + @FieldName + '],'''')))'


EXEC(@SQL)
--PRINT @SQL

Open in new window

ASKER CERTIFIED 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

I just tried to point to a reply but the EE site wouldnt let me edit the question.

Pawan Kumar

Not clear , can you please provide details?

Guy Hengel [angelIII / a3]

yes, like Pawan indicates, the question is: what is the issue you run into?

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial

Guy Hengel [angelIII / a3]

@Pawan Kumar, can you please use the code tags?
thanks

PeterBaileyUk

ASKER

I found this in a previous question ID: 41994873

I was trying to edit this question but you all had been fast so the web page wouldnt let me edit.

PeterBaileyUk

ASKER

Ive just spotted some fields in the tblcompareevents that need calculating dependent on field type.

Your advice would help here, am I better calculating these in vb.net first and then call an insert usp or can i do the following in sql server

PKJD (nvarchar(IOO), not null)  -- this one already has the constraint set
ClientCode (nvarchar(25), null) -- have this in this query
Change (nvarchar(IOO), null) -- have this in this query
ChangeYearMonth (nvarchar(15), null) -- have this in this query BATCH
Prev (nvarchar(IOO), null) -- have this in this query
ActualVariance (int, null) --   if a numerical field abs(prev-change) -- this gets me the variance
VehicleCategory (nvarchar(15), null)  -- have this in this query
Matched (bit, not null) -- this would be a boolean result from this client code and checking if matched to my product code
FieldName (nvarchar(50), null) -- have this in this query

Open in new window

So I think the right question is can I create functions in sql server as I would do in vba?

Vitor Montalvão

You can create computed columns in SQL Server. Would that be enough for you?

PeterBaileyUk

ASKER

it might when i did this in access vba I could find out the field type by number i recall 10 was descriptive 3 & 4 numeric. I dont want to calculate variance based on descriptive. ok i will close off and ask a question on computed columns as its outside of the scope of this question and hopefully I have enough to create the insert later.

I will share the points if you guys are ok with that

Pawan Kumar

You can do as per your choice. Regards,
Pawan

Vitor Montalvão

I will share the points if you guys are ok with that

Peter, the points should be given by you and not by us. The rule is to choose the one or ones that you used as solution.
No points for efforts. This should be used as a KB so in the future people with same question can see what solution worked.
Cheers

PeterBaileyUk

ASKER

I selected the solutions to the question and shared between the two experts who contributed to the answer you and Pawan. Guy's comment was not related to an answer so shared between the two.

I try to be fair but also wary that if a side issue arises like calculated fields then I create a new question as some experts can get annoyed with things like that.