Using update, Insert and select query in a single named query on same database

Ignition
,
1

Can I use update, Insert and select query in a single named query on same database with query type as Query not Update type ?

Insert into dbo.TableToUpdate(field1, field2) values (value1, value2)
UPDATE dbo.TableToUpdate

SET Field1 = value

SELECT * FROM dbo.TableToUpdate

2

I think you can't, not really sure. However, you should separate the named queries or try to combine actions.

What about:

INSERT INTO dbo.TableToUpdate(
	field1,
	field2
)
VALUES (
	value,
	value2
)

It doesn't make much sense to write a value and then instantly changing it.
If you could explain a bit more your problem maybe there is another viable solution for you.

3

DECLARE
@Var1 INT = 1,
@Var2 INT = 374,
@Var3 INT = 308
BEGIN
-- SET NOCOUNT ON
DECLARE @Var4 VARCHAR(20) = NULL;

-- DECLARE @TempTable1 with generic column names
DECLARE @TempTable1 TABLE(
    Column1 VARCHAR(200),
    Column2 NVARCHAR(200),
    Column3 INT,
    Column4 VARCHAR(200),
    Column5 VARCHAR(500),
    Column6 INT,
    Column7 VARCHAR(200),
    Column8 VARCHAR(255),
    Column9 INT,
    Column10 INT,
    Column11 INT,
    Column12 VARCHAR(255),
    Column13 INT,
    Column14 NVARCHAR(200),
    Column15 DATETIME,
    Column16 INT
);

-- INSERT data into @TempTable1
INSERT INTO @TempTable1
SELECT 
    DISTINCT T1.Column1,
    ISNULL(T1.Column2, '') AS Column2,
    ROW_NUMBER() OVER (ORDER BY T1.Column2) AS Column3,
    T2.Column4,
    T2.Column5,
    T3.Column6,
    T3.Column7,
    T1.Column8,
    T4.Column9,
    T1.Column10,
    T1.Column11,
    T5.Column12,
    ISNULL(T1.Column13, '') AS Column13,
    T1.Column13,
    T6.Column14,
    T5.Column15,
    0 AS Column16
FROM 
    Table1 T1 WITH(NOLOCK)
INNER JOIN 
    Table2 T2 WITH(NOLOCK) ON T1.Column10 = T2.Column10
INNER JOIN 
    Table3 T3 WITH(NOLOCK) ON T3.Column6 = T1.Column6
INNER JOIN 
    Table4 T4 WITH(NOLOCK) ON T4.Column10 = T2.Column10
INNER JOIN 
    Table5 T5 WITH(NOLOCK) ON T5.Column2 = T1.Column2
WHERE 
    T5.Column12 = 5
    AND T1.Column11 = @Var1
    AND T5.Column3 = @Var2
    AND T4.Column9 = 1
ORDER BY 
    T5.Column15;

-- Delete records from @TempTable1
DELETE FROM @TempTable1
WHERE Column2 IN (
    SELECT Column2 
    FROM @TempTable1 Temp1
    INNER JOIN Table6 T6 WITH(NOLOCK) ON T6.Column2 = Temp1.Column2
    WHERE T6.Column3 <> @Var2
);

-- DECLARE @TempTable2 with generic column names
DECLARE @TempTable2 TABLE(
    Column1 VARCHAR(200),
    Column2 NVARCHAR(200),
    Column3 INT,
    Column4 VARCHAR(200),
    Column5 VARCHAR(500),
    Column6 INT,
    Column7 VARCHAR(200),
    Column8 VARCHAR(255),
    Column9 INT,
    Column10 INT,
    Column11 INT,
    Column12 VARCHAR(255),
    Column13 INT,
    Column14 NVARCHAR(200),
    Column15 DATETIME,
    Column16 INT
);

-- Insert data into @TempTable2 from @TempTable1
INSERT INTO @TempTable2
SELECT * 
FROM @TempTable1
ORDER BY Column15;

-- Update Column16 in @TempTable2
UPDATE TOP(1) @TempTable2
SET Column16 = 1;

-- Update Column16 in the original table using placeholders
UPDATE Temp
SET Column16 = 2
FROM @TempTable2 Temp
INNER JOIN Table5 T5 WITH(NOLOCK) ON T5.Column2 = Temp.Column2
WHERE T5.Column12 = 3 
AND T5.Column3 = @Var3;

-- Select the final result
SELECT 
    Column1,
    Column3,
    Column2,
    Column4,
    Column5,
    Column6,
    Column7,
    Column8,
    Column9,
    Column10,
    Column11,
    Column12,
    Column13,
    Column14,
    Column15,
    Column16 
FROM @TempTable2;

END; I am trying to use the above sample query

4

See here:

5

looks similar but what i did is created a store procedure and it called it from named query but gave error as time out few times and most of the time it works all of sudden it stops working. And how we can resolve the issue as of now we have switched to prepquery instead of namedquery as namedquery all of sudden stops working.

6

Take a look at the MERGE command for your database.
e.g. SQL Server MERGE
That will give you either INSERT or UPDATE but take a look at the reference posted by @Transistor; With NamedQueries, you need to choose between Query and Update Query; you won't be able to cross that line.

As a personal preference, I create folders (SELECT, INSERT, UPDATE, DELETE) and create a NamedQuery of the table name I'm working with inside each folder. That way, calling a NamedQuery it feels more natural and SQL like as well as providing a little self-documentation of what you are doing when you call:

  • SELECT/TableName
  • INSERT/TableName
  • UPDATE/TableName
1 Like
7

As per Requirement and data I have we need to declare a table and then insert data into it and then parse data from it update and delete it. how to overcome such issue

8

A jython script can do everything you are asking for. Look at the user manual for system.db.* functions