SP that will generate script of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP that will generate script of records

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-12-03 : 03:37:47

Is there a stored procedure that will generate a SQL script of records based on my query?
The script should look like this.



SELECT 'A' AS A, 'B' AS B
union all
SELECT 'Aa' AS A, 'Bb' AS B
union all
SELECT 'AaA' AS A, 'BbB' AS B
union all
SELECT 'aAa' AS A, 'bBb' AS B
union all
SELECT 'AA' AS A, 'BB' AS B
.....

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 03:46:22

This will generate Insert into table(columns) values(data1,data2,...)

http://sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-12-03 : 05:48:56

But i want to write it on a file ".sql"

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 06:06:55

In the above code replace Exec(@execStr) by

Create table InsertData(data varchar(8000))
Insert into InsertData Exec(@execStr)
EXEC Master..xp_cmdShell 'bcp "select * from DBName..InsertData" queryout "C:\data.sql" -c'
Drop table InsertData

Now open the File C:\data.sql and see the script

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-12-03 : 07:19:04

I had this result but there is no file created.



(50 row(s) affected)

output                                                                                                                                                                                                                                                          
---------------------------------------------------------------------------------------- 
Password: 
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DBName..InsertData'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

(6 row(s) affected)

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 07:23:29

You need to Replace DBName by the Database name you are working with

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-12-03 : 07:27:39

hihihihi

tnx....

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 07:28:58

Did you get what you tried to get?

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-12-03 : 07:32:19

[code]

SELECT 'A' AS A, 'B' AS B
union all
SELECT 'Aa' AS A, 'Bb' AS B
union all
SELECT 'AaA' AS A, 'BbB' AS B
union all
SELECT 'aAa' AS A, 'bBb' AS B
union all
SELECT 'AA' AS A, 'BB' AS B

[/code]

Is it possible to put this output into the ".sql" file? Result should be like that.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources