Use Different database in Sql Procedure

Question

I am trying to create dynamic tables with dynamic columns from one database to another using stored procedure. Since i have sequel of queries and conditions i am using it. But facing issue:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

Below is the line where i meet error:

Execute( 'USE ' + @DB1+ ';CREATE TABLE DB2.dbo.'+ @TableName + ' AS (SELECT * [email protected].'+@TableName +')' );

thanks in advance!!

The answer may be in the error message. Can you show the complete script, especially how you are setting the variables — Peter Smith
– Peter Smith, Commented Nov 11, 2017 at 14:35
i tried running it in query editor then no issues, but in procedure since using 2 database , i am having this issue. If i use - CREATE TABLE DB2.dbo.'+ @TableName + ' AS (SELECT * [email protected].'+@TableName .. this is also not working. i need a solution for this. — ankita25
– ankita25, Commented Nov 11, 2017 at 14:39
Can you please update you question rather than adding comments — Peter Smith
– Peter Smith, Commented Nov 11, 2017 at 14:41

shimon893 · Accepted Answer · 2017-11-11 19:00:12Z

1

In your code, you are using @DB1 (second occurrence) inside text (inside of single quotes), but needs to be outside, so it would be parsed as variable.

Also you need to use "select * into", because "crate table as" works only in Azure SQL DataWarehouse and is not correct syntax in MS SQL.

So correct code would look like that:

Execute( 'USE ' + @DB1+ '; SELECT * INTO DB2.dbo.'+ @TableName + ' FROM ' + @DB1 + '.dbo.' + @TableName +')' );

edited Nov 11, 2017 at 19:00

answered Nov 11, 2017 at 18:51

shimon893

3172 silver badges10 bronze badges

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

6 Comments

ankita25 Over a year ago

Yes,this worked for me. The create statement i was using does not work in sql procedure.I changed as per your help and it worked for me.Thanks

shimon893 Over a year ago

@ankita25 if this solved your problem, please mark my answer as correct with tick on the left side of my reply ;)

ankita25 Over a year ago

Ok I did @shimon893.. Is their anyway in SQL Procedure where we can do it with two servers (instead of using 2 database of same server).?

shimon893 Over a year ago

Yes, it is possible, you musi link those servers so they will have access to each other. Here is documentation on string this up: msdn.microsoft.com/pl-pl/library/ff772782(v=sql.110).aspx

ankita25 Over a year ago

Thankyou very much @shimon893 ...I will implement it..You have good knowledge!!

|

William Jones · Accepted Answer · 2017-11-11 15:11:23Z

0

Execute( 'USE ' + @DB1+ '; SELECT * INTO DB2.dbo.'+ @TableName + ' FROM ' + @DB1 + '.dbo.' + @TableName +')' );

answered Nov 11, 2017 at 15:11

William Jones

2141 silver badge5 bronze badges

2 Comments

Dan Guzman Over a year ago

I think it would be helpful if you added an explanation as to why the original code in the question does not work.

ankita25 Over a year ago

Because the 'create table ' statement i was using does not work in ms- sql procedure.

Collectives™ on Stack Overflow

Use Different database in Sql Procedure

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related