does anyone know if there is a way to generate an insert script based
on the records in a table? I want to distribute the contents of a
couple of tables to customers without having to use BCP or DTS.
Greetings Sjaak
I need to write some insert statements, 1 per table, ~100 tables, all having the approximante form:
Select Into TableA Select * From TableB
Except that I need explicit statements:
Select Into TableA Col1, Col2, Col3, ... ColN Values ...
The reason is that I need to preserve the current identity values (it's a replication setup scenario). I can set Identity_Insert On, but then it wants the explicit column names and values.
Is there a wizard or utility that will generate the statements for me? With
100 tables in the db, I'm not looking forward to writing it all :-)
TIA,
Arthur
PS. Given that it's Easter weekend, if you have an answer could you please e me directly? Thanks!
Hey guys...
I just made a little proc to generate inserts for a given table. Sadly, it contains two cursors... :(
Can any of you guys come up with a way without cursors?
Here's the proc:
create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)
set @cols=''
declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table
open colcur
fetch next from colcur into @col
while @@fetch_status=0
begin
select @cols = @cols + ', ' + @col
fetch next from colcur into @col
end
close colcur
deallocate colcur
select @cols = substring(@cols, 3, datalength(@cols))
--select @cols
declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)
select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '
select @sql = @sql + 'values ('''
declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table
open ccur
fetch from ccur into @colname, @coltype
while @@fetch_status=0
begin
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql = @sql + ''', '''
fetch from ccur into @colname, @coltype
end
close ccur
deallocate ccur
select @sql=substring(@sql, 1, datalength(@sql)-3)
select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table
exec (@sql)
I have a question regarding generating SQL script from a database.
If you use SQL Server Management Express, it lets you generate SQL
script that can be used to recreate table structure, stored procedures,
etc. But it does not include the actual data stored in the tables.
I rememeber they used to have this option to generate data script for the table in SQL 2000, but I can not find it in SQL 2005.
I need to move one table from one database to another, but I need to generate SQL Insert Statements...
Good day,
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
Please someone help, this is driving me insane.
Thanks
I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.
Next to the drop down list there is a text box where user can type the some text (translation).
User can add several description
On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)
that's work fine for some language => FR, EN and so on
But for bulgarian (bulgare) and greece there are some problem...
Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?
The value encoded by the user is => Ένωσης. Η διάταξη αυτή
but the result after t sql xpath is => ???s??. ? d??ta?? a?t?
Here is it my sql code where you can find my temporary table and my xml file and my xpath query
declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500))
declare @Translations XML
set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'
-- 2) fill the temporary table with information from the xml file
INSERT INTO @tblTranslation(idDocID, languageID, value)
SELECT
1
, Convert(nvarchar(max), i.query('eleKey/text()')) as colKey
, Convert(nvarchar(max), i.query('eleValue/text()')) as colValue
--
FROM @translations.nodes('/Items/Item') as x(i)
SELECT * FROM @tblTranslation
How to generate insert script of last inserted record in SQL SERVER Table???.. I want use this code for log entry purpose..
View 1 Replies View RelatedI'd I have a problem I'd like to post CREATE TABLE and INSERT statementsthat will create my table and insert data into the table.I can use the scripting feature in Enterprise Manager to generate CREATETABLE scripts.Is there a script I can run that will generate INSERT statements so I caninclude sample data.Thanks
View 1 Replies View RelatedI am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
Hello All,
How are you guys doing? Hope all is well.
This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.
Thanks a bunch!
Mohammed
Dear all,
I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?
Regards,
Pedro Martins
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies View RelatedI have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
View 2 Replies View RelatedI had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies View RelatedIs there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies View RelatedHello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies View Relatedhello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
Hi!
When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?
Jarod
how generate an xml file from data in sqlserver2005
and read it in sql again
One of my first tasks on my current project was to generate all of the SQL scripts needed to rebuild the database. Of course, one of the catches was that everything needed to be separated out (table columns in different script from PKs different from FKs different from DEFAULTs, etc., etc.). And we wanted our own comment block inserted. So, I couldn't just use the Generate SQL Script from Enterprise Manager and be done with it. I was going to need to do some more work. Well, here's one of the scripts I wrote to build the FK scripts. Execute this script, then copy & paste the results into a new window. Break apart into separate .sql files as desired.
NOTE: Be sure to show results in Text (not grid) and change the display options in QA to return more than just 256 characters.
/***************************************************************************************************
Author: Mark Caldwell
Date: 11/15/2002
Descrip: Generate scipt commands to ADD FOREIGN KEY CONSTRAINTS for constraints already in DB.
NOTE: Be sure to set your Tools/Options/Results/Maximum Characters per column to a
large enough number (such as 2000) to display the entire command.
***************************************************************************************************/
SELECT
'----------------------------------------------------------------------------------------------------
-- ' + so2.name + ' to ' + so3.name + '
----------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY(OBJECT_ID(N''' + so1.name + '''), ''IsForeignKey'') = 1 BEGIN
ALTER TABLE ' + so2.name + ' DROP CONSTRAINT ' + so1.name + '
PRINT '' -- DRP - ' + so1.name + '''
END
G' + 'O
ALTER TABLE ' + so2.name + '
ADD CONSTRAINT ' + so1.name + '
FOREIGN KEY (' + ISC1.COLUMN_NAME + ')
REFERENCES ' + so3.name + '(' + ISC2.COLUMN_NAME + ')
PRINT '' -- ADD - ' + so1.name + '''
G' + 'O
'
FROM sysforeignkeys sfk
JOIN sysobjects so1 on sfk.constid = so1.id
JOIN sysobjects so2 on sfk.fkeyid = so2.id
JOIN sysobjects so3 on sfk.rkeyid = so3.id
JOIN INFORMATION_SCHEMA.COLUMNS ISC1 on so2.name = ISC1.TABLE_NAME AND sfk.fkey = ISC1.ORDINAL_POSITION
JOIN INFORMATION_SCHEMA.COLUMNS ISC2 on so3.name = ISC2.TABLE_NAME AND sfk.rkey = ISC2.ORDINAL_POSITION
ORDER BY so2.name
Edited by - AjarnMark on 11/20/2002 02:46:11
The web site I am building is working fine locally, but I am hitting some problems with setting it up on a remote hosting server.First off, how can I generate the sql script to populate the SQL db on the remote server?I am using VS 2005 Standard. Do I need to d/l the SQL Server Express?Once I get that going, I should be able to figure out the rest...but I'll prolly have another question or two.Thanks
View 2 Replies View RelatedHello everyone.
i need to auto generate the user id in id colunm in my sqldatabase table.i want it to generate in this fashion.(mycompanyname-todaydate-number.)eg (ibm-15thfeb-1) (ibm-15thfeb-2) (ibm-16thfeb-1)here i need this user id to be automatically displayed in my web form when doing registration of new user,then only after clicking the savebutton i want all the data along with user id to be inserted into the table in sqldatabase.thanksjack.
Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ............ etc. I've the code below but it's not working in the right way, what's wrong with my code?
Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim ssql As String
Dim Itemid As Integer
Dim updcmd As SqlClient.SqlCommand
Itemid = 0
mysqladap = New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn)
Itemid = (Itemid) + 1
ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')"
updcmd = New SqlClient.SqlCommand(ssql, mySqlConn)
updcmd.ExecuteNonQuery()
lblmsg.Visible = True
End SubAnyone can help me? Thanks.
Are there any scripts available with anyone, for generating SQL scripts for all the objects in a server?
Thanks
I want to transfer some procedures using a generated script. However, the procedures that create pre-existing tables will not be created. Is there a way to shut off the validation so that I can create these procedures without getting errors?
Thanks for the help
Is there a way to write a dts package that will Generate SQL Scripts for a particular database? I am just learning dts and would like to automate this process that I run on weekly basis.
Thanks,
Steve
can I generate excel from stored procedure?
View 2 Replies View Relatedhi all,
how to generate txt file from sql table. I want text file with respective columns without any seperator between columns. and if for an example a column of int having 5 digits but i want it in text file with 10 digits with remaining all as blanks. guide me how to do this.
thnks in adv
This is my first time on sqlteam forum so a big hello to everyone!!
Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file
Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not to how to do that and how to generate it in a txt file?!
Keith
--This procedure will generate the Structure of a table
Create Procedure GenerateScript (
@tableName varchar(100))
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name=@tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select @sql=@sql
+case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Data_Type in (Select datatype from @cols) then '' else '(' end
+case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+
','+case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar)
end when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end
+case when Data_Type in (Select datatype from @cols)then '' else ')' end
+case when Is_Nullable='No' then ' Null,' else ' Not null,' end
from Information_Schema.COLUMNS where Table_Name=@tableName
select @table= 'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
Madhivanan
Failing to plan is Planning to fail
i recently moved from sql server 7 to sql 2005 -- in sql 7 if i wanted to generate an sql script then It would generate a whole script ---
in sql 2005 - when i do generate it seems to attach a file --
I want to generate a script that I can then modify and use to create a new db -- is this possible?
Hi All!
I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.
I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.
Any inputs would be greatly appreciated.
Thanks!