February 17, 2018 at 4:45 am
Hello, I have a table with multi-row select statements, and I want to insert the results into a new table in another database.
Example below:
--- 4 data bases db1, db2 , db3 and dbmain
CREATE DATABASE db1
CREATE DATABASE db2
CREATE DATABASE db3
CREATE DATABASE dbmain
GO
Create table db1.dbo.companies(companiename nvarchar(100),creationdate date)
Create table db2.dbo.companies(companiename nvarchar(100),creationdate date)
Create table db3.dbo.companies(companiename nvarchar(100),creationdate date)
GO
INSERT INTO db1.dbo.companies(companiename, creationdate)
VALUES (N'companie1', '20180215 00:00:00.000')
GO
INSERT INTO db2.dbo.companies(companiename, creationdate)
VALUES (N'companie2', '20180216 00:00:00.000')
GO
INSERT INTO db3.dbo.companies(companiename, creationdate)
VALUES (N'companie3', '20180217 00:00:00.000')
GO
SELECT
'use '+name + ' select companiename from companies' as companiename,
'use '+name + ' select creationdate from companies' as creationdate
into dbmain.dbo.tmpcompaniesdbs
FROM sys .databases
where name like 'db1' or name like 'db2' or name like 'db3'
ORDER BY create_date DESC
GO
Create table dbmain.dbo.companies(companiename nvarchar(100),creationdate date)
GO
DECLARE @var1 NVARCHAR(MAX);
DECLARE @var2 NVARCHAR(MAX);
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT companiename ,creationdate
FROM dbmain.dbo.tmpcompaniesdbs
OPEN Cur
FETCH NEXT FROM Cur INTO @var1, @var2
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO dbmain.dbo.companies(companiename,creationdate)
Exec sp_executesql @var1,@var2
FETCH NEXT FROM Cur INTO @var1,@var2
END
CLOSE Cur
DEALLOCATE Cur;
GO
February 17, 2018 at 9:35 am
Firstly, there is no need for a cursor to do what you are trying to do.
As a rule of thumb, cursors should be avoided.
The following SQL will do the same without using a cursor.INSERT INTO dbmain.dbo.companies(companiename, creationdate)
SELECT companiename, creationdate
FROM dbmain.dbo.tmpcompaniesdbs;
If you insist on using a cursor with Dynamic SQL ....
Change the datat types of @var1 and @var2 to match the data types of the underlying dataDECLARE @var1 NVARCHAR(100);
DECLARE @var2 DATE;
Then build the entire insert statement into the execstring, and pass in the valuesExec sys.sp_executesql
@stmt = N'INSERT INTO dbmain.dbo.companies(companiename,creationdate) values (@var1, @var2);'
, @params = N'@var1 NVARCHAR(100) INT, @var2 DATE'
, @var1 = @var1
, @var2 = @var2;
February 17, 2018 at 9:41 am
Hello,
Thanks !!
It was a fantastic help 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply