INSERT to a table from a database to other (same SQL Server) using Dynamic SQL The Next CEO of Stack OverflowHow to Dynamically change the database using TSQLHow the indexes are managed?Script to query multiple instancesHow to avoid using variables in WHERE clauseOracle GoldenGate add trandata errorsCreate A View Using Dynamic SqlDynamic Pivot SQL tableSyntax Error HelpInvestigating errors from strange queryDynamic SQL isn't updating tableHow to copy all Database from Source to Destination server along with login credentials?
RigExpert AA-35 - Interpreting The Information
Chain wire methods together in Lightning Web Components
Proper way to express "He disappeared them"
Is a distribution that is normal, but highly skewed considered Gaussian?
Unclear about dynamic binding
Is it professional to write unrelated content in an almost-empty email?
Should I tutor a student who I know has cheated on their homework?
What flight has the highest ratio of timezone difference to flight time?
How to install OpenCV on Raspbian Stretch?
How to delete every two lines after 3rd lines in a file contains very large number of lines?
Which one is the true statement?
Why does the flight controls check come before arming the autobrake on the A320?
Is it possible to replace duplicates of a character with one character using tr
Can you be charged for obstruction for refusing to answer questions?
The past simple of "gaslight" – "gaslighted" or "gaslit"?
Is micro rebar a better way to reinforce concrete than rebar?
Can we say or write : "No, it'sn't"?
Rotate a column
Flying from Cape Town to England and return to another province
What was the first Unix version to run on a microcomputer?
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
Why do airplanes bank sharply to the right after air-to-air refueling?
Is there a way to save my career from absolute disaster?
INSERT to a table from a database to other (same SQL Server) using Dynamic SQL
The Next CEO of Stack OverflowHow to Dynamically change the database using TSQLHow the indexes are managed?Script to query multiple instancesHow to avoid using variables in WHERE clauseOracle GoldenGate add trandata errorsCreate A View Using Dynamic SqlDynamic Pivot SQL tableSyntax Error HelpInvestigating errors from strange queryDynamic SQL isn't updating tableHow to copy all Database from Source to Destination server along with login credentials?
I need to copy a record from a Database DBa
, Table Tbl1
to Database DBx
, Table Tbl1
and get the Scope Identity
inside the same SQL Server 2005
server
I have already checked this:
How to Dynamically change the database using TSQL
but my issue is more complicated.
I do want just to execute something in the DBx
being inside DBa
.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.
The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?
Example code:
CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
AS
DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id
DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?
EXEC @exec @sql;
go
Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(
sql-server dynamic-sql
add a comment |
I need to copy a record from a Database DBa
, Table Tbl1
to Database DBx
, Table Tbl1
and get the Scope Identity
inside the same SQL Server 2005
server
I have already checked this:
How to Dynamically change the database using TSQL
but my issue is more complicated.
I do want just to execute something in the DBx
being inside DBa
.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.
The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?
Example code:
CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
AS
DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id
DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?
EXEC @exec @sql;
go
Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(
sql-server dynamic-sql
add a comment |
I need to copy a record from a Database DBa
, Table Tbl1
to Database DBx
, Table Tbl1
and get the Scope Identity
inside the same SQL Server 2005
server
I have already checked this:
How to Dynamically change the database using TSQL
but my issue is more complicated.
I do want just to execute something in the DBx
being inside DBa
.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.
The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?
Example code:
CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
AS
DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id
DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?
EXEC @exec @sql;
go
Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(
sql-server dynamic-sql
I need to copy a record from a Database DBa
, Table Tbl1
to Database DBx
, Table Tbl1
and get the Scope Identity
inside the same SQL Server 2005
server
I have already checked this:
How to Dynamically change the database using TSQL
but my issue is more complicated.
I do want just to execute something in the DBx
being inside DBa
.
I want to copy a record from one database to one of the other (same) databases in the same server and get the scope_identity back.
The Dynamic SQL is a string. What do we do when we want to copy variables of other datatypes as well?
Example code:
CREATE PROCEDURE dbo.pr_consolidation_copy_group @group_id numeric(10,0),@database_to varchar(100)
AS
DECLARE @group_name char(100),@arrival datetime,@departure datetime,@contact_id char(82)
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id
DECLARE @exec nvarchar(max) = QUOTENAME(@database_to) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'INSERT grp(group_name, arrival, departure, contact_id)
SELECT @group_name, @arrival, @departure, @contact_id;SELECT SCOPE_IDENTITY()'; //How do we handle the variables?
EXEC @exec @sql;
go
Get all variables to string and put CONVERT inside the Dynamic SQL?
I hope I can find another solution because my table is around 300 columns :(
sql-server dynamic-sql
sql-server dynamic-sql
edited 10 hours ago
PanosPlat
asked 11 hours ago
PanosPlatPanosPlat
2571410
2571410
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:
CREATE PROCEDURE dbo.pr_consolidation_copy_group (
@group_id numeric(10,0),
@database_to varchar(100)
)
AS
SET NOCOUNT ON;
DECLARE @group_name char(100)
,@arrival datetime
,@departure datetime
,@contact_id char(82);
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id;
DECLARE @sql_cmd NVARCHAR(MAX);
SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
+ 'INSERT grp(group_name, arrival, departure, contact_id)'
+ 'SELECT @group_name, @arrival, @departure, @contact_id;'
+ 'SET @id = SCOPE_IDENTITY()';
DECLARE @params NVARCHAR(MAX);
SET @params = '@group_name CHAR(100),'
+ '@arrival DATETIME,'
+ '@departure DATETIME,'
+ '@contact_id CHAR(82),'
+ '@id INT OUTPUT';
DECLARE @id INT;
EXEC sp_executeSql @stmt = @sql_cmd
, @params = @params
, @group_name = @group_name
, @arrival = @arrival
, @departure = @departure
, @contact_id = @contact_id
, @id = @id OUTPUT;
-- @id is now available to use
END
You can find more information about outputting a variable in the documentation for sp_executeSql.
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233579%2finsert-to-a-table-from-a-database-to-other-same-sql-server-using-dynamic-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:
CREATE PROCEDURE dbo.pr_consolidation_copy_group (
@group_id numeric(10,0),
@database_to varchar(100)
)
AS
SET NOCOUNT ON;
DECLARE @group_name char(100)
,@arrival datetime
,@departure datetime
,@contact_id char(82);
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id;
DECLARE @sql_cmd NVARCHAR(MAX);
SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
+ 'INSERT grp(group_name, arrival, departure, contact_id)'
+ 'SELECT @group_name, @arrival, @departure, @contact_id;'
+ 'SET @id = SCOPE_IDENTITY()';
DECLARE @params NVARCHAR(MAX);
SET @params = '@group_name CHAR(100),'
+ '@arrival DATETIME,'
+ '@departure DATETIME,'
+ '@contact_id CHAR(82),'
+ '@id INT OUTPUT';
DECLARE @id INT;
EXEC sp_executeSql @stmt = @sql_cmd
, @params = @params
, @group_name = @group_name
, @arrival = @arrival
, @departure = @departure
, @contact_id = @contact_id
, @id = @id OUTPUT;
-- @id is now available to use
END
You can find more information about outputting a variable in the documentation for sp_executeSql.
add a comment |
Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:
CREATE PROCEDURE dbo.pr_consolidation_copy_group (
@group_id numeric(10,0),
@database_to varchar(100)
)
AS
SET NOCOUNT ON;
DECLARE @group_name char(100)
,@arrival datetime
,@departure datetime
,@contact_id char(82);
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id;
DECLARE @sql_cmd NVARCHAR(MAX);
SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
+ 'INSERT grp(group_name, arrival, departure, contact_id)'
+ 'SELECT @group_name, @arrival, @departure, @contact_id;'
+ 'SET @id = SCOPE_IDENTITY()';
DECLARE @params NVARCHAR(MAX);
SET @params = '@group_name CHAR(100),'
+ '@arrival DATETIME,'
+ '@departure DATETIME,'
+ '@contact_id CHAR(82),'
+ '@id INT OUTPUT';
DECLARE @id INT;
EXEC sp_executeSql @stmt = @sql_cmd
, @params = @params
, @group_name = @group_name
, @arrival = @arrival
, @departure = @departure
, @contact_id = @contact_id
, @id = @id OUTPUT;
-- @id is now available to use
END
You can find more information about outputting a variable in the documentation for sp_executeSql.
add a comment |
Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:
CREATE PROCEDURE dbo.pr_consolidation_copy_group (
@group_id numeric(10,0),
@database_to varchar(100)
)
AS
SET NOCOUNT ON;
DECLARE @group_name char(100)
,@arrival datetime
,@departure datetime
,@contact_id char(82);
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id;
DECLARE @sql_cmd NVARCHAR(MAX);
SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
+ 'INSERT grp(group_name, arrival, departure, contact_id)'
+ 'SELECT @group_name, @arrival, @departure, @contact_id;'
+ 'SET @id = SCOPE_IDENTITY()';
DECLARE @params NVARCHAR(MAX);
SET @params = '@group_name CHAR(100),'
+ '@arrival DATETIME,'
+ '@departure DATETIME,'
+ '@contact_id CHAR(82),'
+ '@id INT OUTPUT';
DECLARE @id INT;
EXEC sp_executeSql @stmt = @sql_cmd
, @params = @params
, @group_name = @group_name
, @arrival = @arrival
, @departure = @departure
, @contact_id = @contact_id
, @id = @id OUTPUT;
-- @id is now available to use
END
You can find more information about outputting a variable in the documentation for sp_executeSql.
Unless I am mistaken, your problem is not as complicated as you think. From what I understand, you want to take some data from your current database, store it in variables and then insert that into another database, returning the id that was inserted. If so, this will do what you need:
CREATE PROCEDURE dbo.pr_consolidation_copy_group (
@group_id numeric(10,0),
@database_to varchar(100)
)
AS
SET NOCOUNT ON;
DECLARE @group_name char(100)
,@arrival datetime
,@departure datetime
,@contact_id char(82);
SELECT @group_name = group_name ,
@arrival = arrival,
@departure = departure,
@contact_id = contact_id
FROM grp
WHERE group_id = @group_id;
DECLARE @sql_cmd NVARCHAR(MAX);
SET @sql_cmd = 'USE ' + QUOTENAME(@database_to) + ';'
+ 'INSERT grp(group_name, arrival, departure, contact_id)'
+ 'SELECT @group_name, @arrival, @departure, @contact_id;'
+ 'SET @id = SCOPE_IDENTITY()';
DECLARE @params NVARCHAR(MAX);
SET @params = '@group_name CHAR(100),'
+ '@arrival DATETIME,'
+ '@departure DATETIME,'
+ '@contact_id CHAR(82),'
+ '@id INT OUTPUT';
DECLARE @id INT;
EXEC sp_executeSql @stmt = @sql_cmd
, @params = @params
, @group_name = @group_name
, @arrival = @arrival
, @departure = @departure
, @contact_id = @contact_id
, @id = @id OUTPUT;
-- @id is now available to use
END
You can find more information about outputting a variable in the documentation for sp_executeSql.
edited 8 hours ago
answered 9 hours ago
Mr.BrownstoneMr.Brownstone
9,75232342
9,75232342
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233579%2finsert-to-a-table-from-a-database-to-other-same-sql-server-using-dynamic-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown