SQL script to generate another [INSERT] SQL script

Question

I have a weird SQL situation in which I need to create a SQL script that I would run against database A and it would output another SQL script that would contain INSERT statements which I can then run against database B and upload the data (essentially an Export from A to B but a longer way).

I was wondering if anyone has worked on this an can suggest a way to generate INSERT statements that would contain data from [Database A].[Table Student] which can then be uploaded to [Database B].[Table Student].

Thanks a lot.

Depending on the version, there are easier ways. For example you could export a .dacpac — Nick.Mc
– Nick.Mc, Commented Nov 6, 2017 at 11:07

Tony C · Accepted Answer · 2017-11-06 10:56:22Z

1

You can concat the values from student into an insert string like:

SELECT concat('Insert INTO Students VALUES(', Col1,', ', '''', Col2, '''', ')')
     AS [Details]
     FROM Student
     WHERE ...

N.B The four single quotes is to create a single quote before and after varchar columns for example.

To generate a new SQL script from this, I'd create a cmd file with something like:

SET SERVER=YOUR_SERVER
SET DATABASE=YOUR_DB

sqlcmd -S %SERVER% -d %DATABASE% -h -1 -i Script.sql -o NewScript.sql -W

answered Nov 6, 2017 at 10:56

Tony C

511 bronze badge

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

1 Comment

Amar Over a year ago

This is just what I needed...Thanks a lot for your help.

Alfaiz Ahmed · Accepted Answer · 2017-11-06 09:15:08Z

0

-- You Can Simple Use insert into statement

INSERT INTO [Database B].[Table Student]
SELECT * FROM [Database A].[Table Student] 
WHERE StudentID NOT IN (SELECT  StudentID FROM  [Database B].[Table Student])

answered Nov 6, 2017 at 9:15

Alfaiz Ahmed

1,7261 gold badge13 silver badges17 bronze badges

Comments

Yogesh Sharma · Accepted Answer · 2017-11-06 09:15:21Z

0

Here is one way that would insert data from one database table to other database table

INSERT INTO [DestinationDB].[dbo].Student
SELECT * FROM [SourceDB].[dbo].Student
GO

answered Nov 6, 2017 at 9:15

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Amar Over a year ago

Hi, thanks for replying. I know this is a much simpler and "more logical" way but I need to create a script that would output another script containing INSERT stmts. Weird, I know :(.

gotqn · Accepted Answer · 2017-11-06 09:29:48Z

0

If you have access to the both databases you can do it in one query like this:

INSERT INTO [Database A].[Table Student]
OUTPUT inserted.*
INTO [Database B].[Table Student]
SELECT *
FROM [newdata];

answered Nov 6, 2017 at 9:29

gotqn

43.9k47 gold badges167 silver badges256 bronze badges

Collectives™ on Stack Overflow

SQL script to generate another [INSERT] SQL script

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related