August 23, 2010 at 3:02 am
Hi All,
How can i generate an insert query script from sqlserver 2000 tables. Can anybody help me
Regards
Balaji.G
August 23, 2010 at 3:15 am
it means you know how to do it in 2005/2008. Why not use that database in SSMS instead of EM.
In EM, you can try this :-
1) Right Click on table and drag it to query analyzer.
2) It will give you the option to script for create,delete,alter.
I think this is your another weired question 😉 :w00t:
----------
Ashish
August 23, 2010 at 6:17 am
Hi Ashish
I'm asking about sqlserver 2000 production database..
August 23, 2010 at 6:22 am
unfortunately I dont have 2000.
but once you open query analyser and on your left if you open object browser, you will be able to do whatever I already suggested you referring as EM(Enter. Manaer which is in 2000)
----------
Ashish
August 23, 2010 at 6:30 am
Failing that, would something like this do?
SELECT 'INSERT INTO yournewtable(Col1,Col2,Col3) SELECT '
+ Quotename(col1, '''') + ','
+ Quotename(col2, '''') + ','
+ Quotename(col3, '''') + ' UNION ALL'
FROM yourtable
August 26, 2010 at 4:52 am
balaji.ganga (8/26/2010)
Hi All,How to generate a insert query script in sqlserver 2000. In Sql server 2000 don't have option to
generate a script.
Example
select empname,empid from employee
select 'insert into employee (empname,Empid) values('
empname +','+ empid ')' from employee
I need a following format output like
insert into employee(empname,empid) values('1000','Balaji') like that..
While i'm executing the above script. its getting error.
Syntax Error..
I would be greatly appreciated if any one help me out to solve this issue..
With Thanks & Regards
Balaji.G
You're missing a +
SELECT 'INSERT INTO employee (empname,empid) VALUES ('
+ empname + ',' + empid + ')'
FROM employee
August 27, 2010 at 5:55 am
Hi,
I dont think you can do this from sql server 2000.
If you have 2005 management studio then do the below to generate insert query
Right click on a table then choose --> SCRIPT TABLE AS --> INSERT TO
Thanks,
August 30, 2010 at 11:42 pm
Hi,
I used + symbol in front of the column name.. its error occurred. Any other alternate solution is there? or else.. any tool is there?
Pls suggest me..
Thanks & Regards
Balaji.G
August 30, 2010 at 11:59 pm
Hi
-- created sample table.
create table product
(cust varchar(25),
product varchar(20),
qty int)
-- inserting sample record
insert into product (cust,product,qty) values('KATE','SODA',6)
-- script to generate dynamic script
select 'insert into Product (cust,product,qty) values('+
''''+cust+'''' +','+''''+ product+''''+','+convert(varchar(10),qty)+')' from Product
it will works. if any issues reply.
Thanks
Siva Kumar J
August 31, 2010 at 12:55 am
Hi Siva,
select 'insert into sam(Id,Name,type,Email,Versionnumber)
values(' + ""id""+','+""Name""+','+ ""type""+','+""Email""+','+""Versionnumber""+ ')'
from sam
when i ran the above query.. the following error occurred
cannot use empty objects or column names.Use a single space if necessary..
then i removed one quotes of each column..
select 'insert into sam(Id,Name,type,Email,Versionnumber)
values(' + "id"+','+"Name"+','+ "type"+','+"Email"+','+"Versionnumber"+ ')'
from sam
Implicit conversion from one varchar to other varchar column cannot be performed..
because the collation of the value is unresolved.. due to collation conflict..
Then i used to sP_help sam
ColumnNameCollation
ID SQL_Latin_General_CP1_cI_AS
Name SQL_Latin_General_CP1_cI_AS
Type SQL_Latin_General_CP1_cI_AS
Version Latin_General_CP1_cI_AS
Email Latin_General_CP1_cI_AS
Could you pls suggest.. how to fix it.. because i want to build an insert statement scripts
for the most crucial objects.. then i insert those objects into Oracle environment..
Thanks & Regards
Balaji.G
August 31, 2010 at 2:17 am
balaji.ganga (8/31/2010)
Hi Siva,select 'insert into sam(Id,Name,type,Email,Versionnumber)
values(' + ""id""+','+""Name""+','+ ""type""+','+""Email""+','+""Versionnumber""+ ')'
from sam
when i ran the above query.. the following error occurred
cannot use empty objects or column names.Use a single space if necessary..
What error occurred with this?
SELECT 'INSERT INTO sam(Id,Name,type,Email,Versionnumber) VALUES(' + id + ',' + name + ',' + TYPE + ',' + email + ',' +
versionnumber + ')'
FROM sam
August 31, 2010 at 2:29 am
Hi balagi,
Are you using stored procedure? or building insert script in other programmin language.
try this
1. declare variables.
2. set your values
3. insert
declare @Id int,@Name varchar(50),@type int,@Email varchar(100),@Versionnumber varchar(10)
--SET YOUR VALUES
SET @Name = 'Balaji.'
...
...
insert into sam(Id,Name,type,Email,Versionnumber)
values(@Id,@Name,@type,@Email,@Versionnumber)
Thanks,
Raj
September 3, 2010 at 5:16 am
Hi
don't use double codes. use single codes. You have not mentioned concatination symbol (+) for each field.
select 'insert into sam(Id,Name,type,Email,Versionnumber)
values(' + ''''+id+''+','+''''+Name+''''+','+ ''''+type+''''+','+''''+Email+''''+','+''''+Versionnumber+''''+ ')'
from sam
Thanks
Siva Kumar J
September 6, 2010 at 4:17 am
Dear Siva,
Thanks for your great and timely help.. Its working fine..
Thanks & Regards
Balaji.G
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply