How to create Insert with Select query in a variables using SQL Server

Question

How to create an insert with select query in a variables using SQL Server?

Here for example:

DECLARE @sqlCommand nvarchar(MAX)
DECLARE @odbname varchar(30)
DECLARE @m VARCHAR(20)
DECLARE @id VARCHAR(20)
DECLARE @br VARCHAR(30)
DECLARE @ndbname VARCHAR(30)

SET @ndbname='databasename'
SET @id = 2

SET @odbname = 'olddatabasename'
SET @br = 2

SET @m = 9

DECLARE @insert VARCHAR(MAX)
SET @insert = 'INSERT INTO'+ @ndbname+'.[pm]([pmId],[pmCode],[pmName])'  

EXEC (@insert)

SET @sqlCommand = 'SELECT @id AS spID,[spCode],[spName] `enter code here`FROM' + @0dbname+'.[sp] where spbID = @br and spID = @m'

EXECUTE sp_executesql @sqlCommand, N'@br nvarchar(75),@m nvarchar(75),@id VARCHAR(20)',@br = @br,@m=@m,@id=@id

Actually I needed a select query executed in insert query ie, selected data inserted in to another database table

The syntax is: insert into table_name1 select col_names from table_name2. In this case, the structure of that table1 and selected columns structure from table2 should be same. — Sankar
– Sankar, Commented Feb 11, 2016 at 7:16
Can you acess both database from query window then you can use INsert into TODBname.SchemaName.TableName Select columns list from FROMDBname.SchemaName.TableName — Neeraj Prasad Sharma
– Neeraj Prasad Sharma, Commented Feb 11, 2016 at 7:20

gofr1 · Accepted Answer · 2016-02-11 07:29:04Z

1

SET @insert='INSERT INTO'+ @ndbname+'.[pm]([pmId],[pmCode],[pmName])'  
SET @sqlCommand = @insert + ' ' +'SELECT @id AS spID,[spCode],[spName] `enter code here`FROM' + @0dbname+'.[sp] where spbID = @br and spID = @m'
EXECUTE sp_executesql @sqlCommand, N'@br nvarchar(75),@m nvarchar(75),@id VARCHAR(20)',@br = @br,@m=@m,@id=@id

answered Feb 11, 2016 at 7:29

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gottfried Lesigang Over a year ago

This is really working fine? Missing spaces, invalid variable name "@0dbname"... schema names... ?

gofr1 Over a year ago

Just a space, it is not very important in this case.

Gottfried Lesigang Over a year ago

Did you even check this out? @user2728653 seems to be happy, but I cannot imagine that this is working at all... nvm and Happy Coding!

gofr1 Over a year ago

I think user2728653 understand that INSERT and SELECT must be one after other in order to insert what he needed. @Sankar Raj later give a good explanation, and only example needed to be posted.

Gottfried Lesigang Over a year ago

@IlyaOdintsov, Your "example" produces wrong code. At least the wrong three-part-qualified name (databasename.schemaname.tablename) is a major flaw... But we don't have to be that picky. OP is happy and so should be we :-)

Gottfried Lesigang · Accepted Answer · 2016-02-11 08:18:41Z

There are quite a few flaws:

You try to execute the INSERT alone
If you state a dbName, you must specify the schema (is it "dbo"?)
You're letting out spaces

If you change to this

SET @insert='INSERT INTO'+ @ndbname+'.[pm]([pmId],[pmCode],[pmName])'  
SET @sqlCommand = @insert + ' SELECT @id AS spID,[spCode],[spName] `enter code here`FROM' + @odbname+'.[sp] where spbID = @br and spID = @m'
PRINT @sqlCommand

You'll get this:

INSERT INTOdatabasename.[pm]([pmId],[pmCode],[pmName]) SELECT @id AS spID,[spCode],[spName] `enter code here`FROMolddatabasename.[sp] where spbID = @br and spID = @m

But it should be something like this

INSERT INTO databasename.[schema].[pm]([pmId],[pmCode],[pmName]) 
SELECT @id,[spCode],[spName] 
FROM olddatabasename.[schema].[sp] 
where spbID = @br and spID = @m

General hints:

Don't use dynamic SQL if you don't need it
If you build dynamic sql don't try to execute it. Rather print it, copy it to a query window and analyze if it is OK

Collectives™ on Stack Overflow

How to create Insert with Select query in a variables using SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related